group_concat() function not working in preprocessor

[parthaghosh]

Hello everyone,

We have a specific requirement to populate a list of values in comma separated format in a customproperty, during a user import event.
we are trying to build this in preprocessor, but its throwing error around 'Group_concat()' function.
however the the same query is running fine in data analyzer.

preprocessor query is : UPDATE NEWUSERDATA SET customproperty2 = SELECT group_concat(distinct USERNAME SEPARATOR ',') FROM CURRENTUSERS where CURRENTUSERS.customproperty1=NEWUSERDATA.customproperty1

can anyone confirm, if this fucntion really works in preprocessor or not?

[parthaghosh]

Error in Users Import - Error while processing data: 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 'SELECT group_concat(distinct USERNAME SEPARATOR ',') FROM TEMPUSERS_14353 where ' at line 1

[AmitM · Accepted solution]

HI @parthaghosh , to confirm group_concat works in preprocessor. Here is one that has been working for us

"UPDATE NEWUSERDATA SET customproperty47= (SELECT group_concat(username) FROM CURRENTUSERS where CURRENTUSERS.customproperty45='XXXX')"

in your case, separator I think by default is comma so dont need that and add brackets.

Try this UPDATE NEWUSERDATA SET customproperty2 = (SELECT group_concat(distinct USERNAME) FROM CURRENTUSERS where CURRENTUSERS.customproperty1=NEWUSERDATA.customproperty1)

Thanks,

Amit 

If this answers your query, Please ACCEPT SOLUTION and give KUDOS.

[rushikeshvartak · Accepted solution]

UPDATE NEWUSERDATA
SET customproperty2 = (
SELECT GROUP_CONCAT(DISTINCT USERNAME SEPARATOR ',')
FROM CURRENTUSERS
WHERE CURRENTUSERS.customproperty1 = NEWUSERDATA.customproperty1
);

[parthaghosh]

Thank you Amit and Rushikesh. It works 🙂