Update query doubt

[seemran17]

Hey team,

The usecase is that we need to populate a certain endpoint (App A) accounts' customproperty31 with some value. This endpoint has other endpoint names as account names - like app1, app2 are different accounts on this endpoint, and from the tasks table, we want to pull in certification revoke tasks and populate the cp31 of these accounts thus.

App A - main endpoint

App1, App2 - different accounts on App A and are also actual endpoints that are part of campaigns

It's a little complicated to explain, but this is the query I have:

update
accounts a
join arstasks ar on ar.endpoint = a.customproperty1
join account_entitlements1 a1 on a1.accountkey = ar.ACCOUNTKEY
join entitlement_values ev on ev.ENTITLEMENT_VALUEKEY = a1.entitlement_valuekey
set
a.customproperty31 = GROUP_CONCAT("[",ar.ACCOUNTNAME,"+",ev.ENTITLEMENT_VALUE,"]")
where
a.endpointkey in ('417') and ar.endpoint = a.customproperty1 and
ar.source in ('CERTIFICATION')
and ar.ENDPOINT in ('349','346','35','366','71','343','388') group by ar.endpoint

where ('349','346','35','366','71','343','388') are the endpointkeys of the endpoints that are accounts on the main endpoint (endpointkey = 417). The query is running successfully in custom query job but the values themselves are not being populated. Does anyone have any input on this?

Thanks!

[Darshanjain]

Hi @seemran17 

Group_concat cannot be used in update query in mysql in this way, you would need write it in a different query to get this updated.

Sample:

UPDATE accounts a
JOIN (
SELECT ar.endpoint, GROUP_CONCAT(DISTINCT CONCAT('[', ar.ACCOUNTNAME, '+', ev.ENTITLEMENT_VALUE, ']')) AS concatenated_values
FROM arstasks ar
JOIN account_entitlements1 a1 ON a1.accountkey = ar.ACCOUNTKEY
JOIN entitlement_values ev ON ev.ENTITLEMENT_VALUEKEY = a1.entitlement_valuekey
WHERE ar.source = 'ZERODAY' AND ar.ENDPOINT = 2
GROUP BY ar.endpoint
) AS sub ON subq.endpoint = a.endpointkey
SET a.customproperty31 = sub.concatenated_values
WHERE a.endpointkey = 2;

Note: Check the select query inside to get the exact result set and then try to run the update query as per your need.

Thanks

Darshan