Unable to update CP using customquery

[Vinit556]

Hello,

I am trying to put value of customproperty19 from AD endpoint to user's Customproperty52 using custom query job.

Query:

Update users u set u.customproperty52 = (SELECT a.customproperty19 FROM ACCOUNTS a, users u, USER_ACCOUNTS UA, ENDPOINTS EP where u.username='100767' AND a.ACCOUNTKEY = UA.ACCOUNTKEY AND u.USERKEY = UA.USERKEY AND a.STATUS = 1 AND a.ENDPOINTKEY = EP.ENDPOINTKEY AND EP.ENDPOINTKEY IN (SELECT ENDPOINTKEY FROM ENDPOINTS WHERE UPPER(ENDPOINTNAME) = 'Singlife_Dev_AD_EP') AND u.STATUSKEY = 1) where u.username='100767'

but this job is getting failed.

Any idea, am i missing anything?

[rushikeshvartak]

Update users u,ACCOUNTS a, USER_ACCOUNTS UA, ENDPOINTS EP  set u.customproperty52 = a.customproperty19 where a.ACCOUNTKEY = UA.ACCOUNTKEY AND u.USERKEY = UA.USERKEY AND a.STATUS = 1 AND a.ENDPOINTKEY = EP.ENDPOINTKEY AND (EP.ENDPOINTNAME) = 'Singlife_Dev_AD_EP' AND u.STATUSKEY = 1 and u.username='100767'

Your query says upper of endpointname but name in right hand is not upper

[Belwyn]

Hi @Vinit556 

  Please try breaking down your query like run the search query in the data analyser to see if the select query works, then have the condition checked.  

Thanks & Regards, 

Belwyn.