SQL2 Update Query

Avatar

Avatar

deana66659071

Avatar

deana66659071

deana66659071

19-10-2016

I'm using AEM 6.2.

Is it possible to update content using a SQL2 query with the Tools, Query, SQL2 tool?

Example Select

select * from [nt:unstructured] as p where isdescendantnode (p, [/content/]) AND [sling:resourceType] like '/apps/project/component%'

and Update the content to '/apps/project/component2'

Thank you in advance for your help.

-Dean

Accepted Solutions (1)

Accepted Solutions (1)

Avatar

Avatar

Anton_Smulskiy

Avatar

Anton_Smulskiy

Anton_Smulskiy

19-10-2016

From description - no.

One more option is a bulk editor

https://docs.adobe.com/docs/en/aem/6-1/administer/operations/bulk-editor.html

  1. Make a query
  2. Export results to excel file
  3. Filter/update properties in excel file
  4. Import excel file

But, be aware. From documentation: 

When you re-import content, you erase any previous content for those nodes. Be careful not to overwrite important information.

Answers (7)

Answers (7)

Avatar

Avatar

deana66659071

Avatar

deana66659071

deana66659071

20-10-2016

~~Anton Smulskiy ...

This is probably the best approach for what we're looking for.

Thanks to all for your responses.

-Dean

Avatar

Avatar

smacdonald2008

Total Posts

12.7K

Likes

1.4K

Correct Reply

2.3K

Avatar

smacdonald2008

Total Posts

12.7K

Likes

1.4K

Correct Reply

2.3K
smacdonald2008

20-10-2016

Look at Joerg's answer in this old community thread:

https://forums.adobe.com/thread/1158311

Avatar

Avatar

Jitendra_S_Toma

Avatar

Jitendra_S_Toma

Jitendra_S_Toma

19-10-2016

As per Sling Query documentation, updating directly from query isn't available as of now.

https://github.com/Cognifide/Sling-Query/wiki/Method-list

deana66659071 wrote...

I'm using AEM 6.2.

Is it possible to update content using a SQL2 query with the Tools, Query, SQL2 tool?

Example Select

select * from [nt:unstructured] as p where isdescendantnode (p, [/content/]) AND [sling:resourceType] like '/apps/project/component%'

and Update the content to '/apps/project/component2'

Thank you in advance for your help.

-Dean

 

Avatar

Avatar

deana66659071

Avatar

deana66659071

deana66659071

19-10-2016

This is a GREAT tool.  Do you happen to know if I can update ANY/all properties within a given path?

Example:

Path:  /content/project/subproject

reference property: sling:resourceType

reference property value: foundation/components/reference

property:  jcr:title

new value:  This is the new value

I'm trying to find all of the sling:resourceType='foundation/components/reference' with a property of jcr:title and update the jcr:title to 'This is a new value'

Thanks,

-Dean

Avatar

Avatar

Anton_Smulskiy

Avatar

Anton_Smulskiy

Anton_Smulskiy

19-10-2016

Check this tool. 

https://adobe-consulting-services.github.io/acs-aem-tools/csv-resource-type-updater.html

I think this is what you are looking for.

Avatar

Avatar

deana66659071

Avatar

deana66659071

deana66659071

19-10-2016

Can you do it within the query?

Similar to SQL ... UPDATE XXX where ...

Do I have to use JCR API?

-Dean

Avatar

Avatar

smacdonald2008

Total Posts

12.7K

Likes

1.4K

Correct Reply

2.3K

Avatar

smacdonald2008

Total Posts

12.7K

Likes

1.4K

Correct Reply

2.3K
smacdonald2008

19-10-2016

Yes - when using JCR SQL2 - you get back a result set of nodes. You can then update the nodes using JCR API.

Invoke the node's setProperty method to modify the values.