Modify JSON keeps Updating value

[mbh_it]

Hello All,

I want to set CP20 to UUID of users which are getting access to specific application, when I am putting query like below, it keeps Updating the CP20 though its one time activity I want to do for each user, of course new user it may need to set CP20 or may not depending on CP31 value.

Can you please help me set CP20=UUID when CP31 is set to FISIBS, so that it does not keep updating every time, I tried following two but no luck !

UPDATE NEWUSERDATA LEFT JOIN CURRENTUSERS ON NEWUSERDATA.employeeid = CURRENTUSERS.employeeid SET NEWUSERDATA.CUSTOMPROPERTY20 = CASE WHEN (CURRENTUSERS.CUSTOMPROPERTY31 = 'FIS IBS' AND (CURRENTUSERS.CUSTOMPROPERTY20 is NULL OR CURRENTUSERS.CUSTOMPROPERTY20='')) THEN UUID() END

UPDATE NEWUSERDATA LEFT JOIN CURRENTUSERS ON NEWUSERDATA.employeeid = CURRENTUSERS.employeeid SET NEWUSERDATA.CUSTOMPROPERTY20=UUID() where (CURRENTUSERS.CUSTOMPROPERTY31 = 'FIS IBS' AND (CURRENTUSERS.CUSTOMPROPERTY20 is NULL OR CURRENTUSERS.CUSTOMPROPERTY20=''))

Even I tried below query(like just set for all user) which sets in one import and makes blank in next import

UPDATE NEWUSERDATA LEFT JOIN CURRENTUSERS ON NEWUSERDATA.employeeid = CURRENTUSERS.employeeid SET NEWUSERDATA.CUSTOMPROPERTY20 =CASE WHEN (CURRENTUSERS.CUSTOMPROPERTY20 is null) THEN UUID() END

Thanks

Mahesh

[rushikeshvartak]

UPDATE NEWUSERDATA
LEFT JOIN CURRENTUSERS ON NEWUSERDATA.employeeid = CURRENTUSERS.employeeid
SET NEWUSERDATA.CUSTOMPROPERTY20 = CASE
WHEN CURRENTUSERS.CUSTOMPROPERTY31 = 'FIS IBS'
AND (NEWUSERDATA.CUSTOMPROPERTY20 IS NULL OR NEWUSERDATA.CUSTOMPROPERTY20 = '')
THEN UUID()
ELSE NEWUSERDATA.CUSTOMPROPERTY20
END;

[mbh_it]

Hi @rushikeshvartak 

Appreciate your inputs here

Apologies, earlier I sent it too early. The Updates keep same value Updating as below

Thanks

Mahesh

[rushikeshvartak]

UPDATE NEWUSERDATA
LEFT JOIN CURRENTUSERS ON NEWUSERDATA.employeeid = CURRENTUSERS.employeeid
SET NEWUSERDATA.CUSTOMPROPERTY20 = CASE
WHEN CURRENTUSERS.CUSTOMPROPERTY31 = 'FIS IBS'
AND (NEWUSERDATA.CUSTOMPROPERTY20 IS NULL OR NEWUSERDATA.CUSTOMPROPERTY20 = '')
THEN UUID()
ELSE NEWUSERDATA.CUSTOMPROPERTY20
END WHERE CURRENTUSERS.CUSTOMPROPERTY20 IS NULL

[mbh_it · Accepted solution]

Hi @rushikeshvartak 

It did not work, Thanks very much for providing inputs . After doing some trials ,following I used and it is now working.

UPDATE NEWUSERDATA LEFT JOIN CURRENTUSERS ON NEWUSERDATA.employeeid = CURRENTUSERS.employeeid SET NEWUSERDATA.CUSTOMPROPERTY20 = CASE WHEN (CURRENTUSERS.CUSTOMPROPERTY31 = 'FIS IBS' AND (CURRENTUSERS.CUSTOMPROPERTY20 is NULL OR CURRENTUSERS.CUSTOMPROPERTY20='')) THEN UUID() ELSE CURRENTUSERS.CUSTOMPROPERTY20 END

Thanks

Mahesh