Update Account name into User's CP value using customquery.

[Mervinr]

Hi All,

Need help in updating the Active directory's account name into any of the custom property values of the user using customquery.

we tried following query it is not working 

UPDATE USERS AS US INNER JOIN user_accounts AS ua ON US.userkey = ua.userkey INNER JOIN accounts AS acc ON ua.accountkey = acc.accountkey SET US.customproperty40 = acc.name Where acc.status = 1 and acc.name not like US.username and acc.endpointkey in (Select endpointkey from endpoints where endpointname like 'Active Directory')

thanks

Mervin

[Raghu]

@Mervinr  your query looks like correct , may i know what is error in log? share log

Below only one endpoint applying script u can use direct '=' right

endpointkey in (Select endpointkey from endpoints where endpointname ='Active Directory')

[Mervinr]

Sure,

I have changed from like to = but not luck.

In Application log am seeing below error

java.sql.BatchUpdateException: Data truncation: Truncated incorrect DOUBLE value: 'Manually Provisioned'

[Raghu · Accepted solution]

try below @Mervinr 

UPDATE USERS AS US
INNER JOIN user_accounts AS ua ON US.userkey = ua.userkey
INNER JOIN accounts AS acc ON ua.accountkey = acc.accountkey
SET US.customproperty40 = acc.name Where acc.status  in ('Manually Provisioned','Active','1') and acc.name not like US.username and acc.endpointkey in (Select endpointkey from endpoints where endpointname='Active Directory')

[Mervinr]

Thanks you so much, it works.