Custom Query job failing for updating entitlement custom properties

[shubhangsinha_]

Hi All,

I have written a sql query to update entitlement attributes using CTE(Common Table expression) but I am getting a syntax error in the Saviynt logs which says- "groovy.sql.Sql.withBatch Error during batch execution: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'testtable  AS". Would you extremely helpful if any sort of alternative method/correction could be suggested. 

Query I am using - 

WITH testtable AS (select fieldname, fieldkey, minvalue, mxvalue, entitlement_values.ENTITLEMENT_VALUEKEY,ENTITLEMENT_VALUE from fields left join entitlement_objects
ON fields.fieldkey = entitlement_objects.field_key left join entitlement_values ON entitlement_objects.ENTITLEMENT_VALUEKEY = entitlement_values.ENTITLEMENT_VALUEKEY where ENTITLEMENT_VALUE like 'zd%' AND fieldkey=3)
Update entitlement_values set customproperty10 = testtable.minvalue where testtable.ENTITLEMENT_VALUE = entitlement_values.entitlement_value and ENTITLEMENTTYPEKEY=13

[rushikeshvartak]

Query should start with insert/update , use joins

[shubhangsinha_]

Only doubt I have is that how will I update the customproperty of an entitlement with the minvalue (as it is coming from joins for 3 tables) and it does not have any specific name.

[rushikeshvartak]

You can use inner query

[shubhangsinha_]

I tried using the below query and the error I got is -"{"log":"25-Nov-2022 04:27:17.138 WARNING [quartzScheduler_Worker-6] groovy.sql.Sql.withBatch Error during batch execution: You can't specify target table 'entitlement_values' for update in FROM clause\n","stream":"stderr","time":"2022-11-25T04:27:17.138839634Z"}"

update entitlement_values set entitlement_values.customproperty30 = (select minvalue from fields left join entitlement_objects
ON fields.fieldkey = entitlement_objects.field_key left join entitlement_values ON entitlement_objects.ENTITLEMENT_VALUEKEY = entitlement_values.ENTITLEMENT_VALUEKEY where ENTITLEMENT_VALUE like 'zd%' AND fieldkey=3) where entitlement_values.ENTITLEMENTTYPEKEY=13

[rushikeshvartak]

update entitlement_values e set e.customproperty30 = (select minvalue from fields left join entitlement_objects o
ON fields.fieldkey = o.field_key left join entitlement_values v ON o.ENTITLEMENT_VALUEKEY = v.ENTITLEMENT_VALUEKEY where e.ENTITLEMENT_VALUE like 'zd%' AND fieldkey=3) where e.ENTITLEMENTTYPEKEY=13

[shubhangsinha_]

Getting this in the logs-

{"log":"25-Nov-2022 04:49:02.466 WARNING [quartzScheduler_Worker-3] groovy.sql.Sql.withBatch Error during batch execution: You can't specify target table 'e' for update in FROM clause\n","stream":"stderr","time":"2022-11-25T04:49:02.466607259Z"}

2022-11-25, 10:19 am

ecm-worker

[rushikeshvartak]

update fields f left join entitlement_objects o left join entitlement_values e set e.customproperty30 = f.minvalue where f.fieldkey = o.field and o.ENTITLEMENT_VALUEKEY = e.ENTITLEMENT_VALUEKEY and e.ENTITLEMENT_VALUE like 'zd%' AND f.fieldkey=3 and e.ENTITLEMENTTYPEKEY=13

[shubhangsinha_]

groovy.sql.Sql.withBatch Error during batch execution: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'set e.customproperty30 = f.minvalue where f.fieldkey = o.field and o.ENTITLEMENT' at line 1\n","stream":"stderr","time":"2022-11-25T05:21:20.749516532Z"}

[rushikeshvartak]

update
fields f,
entitlement_objects o,
entitlement_values e
set
e.customproperty30 = f.minvalue
where
f.fieldkey = o.field
and o.ENTITLEMENT_VALUEKEY = e.ENTITLEMENT_VALUEKEY
and e.ENTITLEMENT_VALUE like 'zd%'
AND f.fieldkey = 3
and e.ENTITLEMENTTYPEKEY = 13

[shubhangsinha_]

Error Occured in customQueryExecution - true and exception - java.sql.BatchUpdateException: Unknown column 'o.field' in 'where clause'\n","stream":"stdout","time":"2022-11-25T05:33:00.787131894Z"}

2022-11-25, 11:03 am

{"log":"2022-11-25 05:33:00,787 [quartzScheduler_Worker-6] DEBUG jobs.CustomQueryJob - Error Occured for customQueryExecution - true\n","stream":"stdout","time":"2022-11-25T05:33:00.787395771Z"}

[rushikeshvartak]

update
fields f,
entitlement_objects o,
entitlement_values e
set
e.customproperty30 = f.minvalue
where
f.fieldkey = o.field_key
and o.ENTITLEMENT_VALUEKEY = e.ENTITLEMENT_VALUEKEY
and e.ENTITLEMENT_VALUE like 'zd%'
AND f.fieldkey = 3
and e.ENTITLEMENTTYPEKEY = 13

[shubhangsinha_]

{"log":"2022-12-06 11:04:40,303 [quartzScheduler_Worker-1] DEBUG services.SaviyntCommonUtilityService - Error Occured in customQueryExecution - true and exception - java.sql.BatchUpdateException: Unknown column 'f.minvalue' in 'field list'\n","stream":"stdout","time":"2022-12-06T11:04:40.303249252Z"}

[rushikeshvartak]

This is very basis error you should learn SQL

update
fields f,
entitlement_objects o,
entitlement_values e
set
e.customproperty30 = o.minvalue
where
f.fieldkey = o.field_key
and o.ENTITLEMENT_VALUEKEY = e.ENTITLEMENT_VALUEKEY
and e.ENTITLEMENT_VALUE like 'zd%'
AND f.fieldkey = 3
and e.ENTITLEMENTTYPEKEY = 13

[shubhangsinha_]

I did change the column name after I replied you and it worked , thank you so much for the help. Cheers! 

[rushikeshvartak · Accepted solution]

Please accept working solution to help others