Using group_concat with update statement in MySql

Question

I am trying to execute the below update query using GROUP_CONCAT but it is failing.

Can anyone kindly help me in correcting it, moreover the statement must always start with the “update” keyword is the catch here as well.

UPDATE firstEntry f, 
       objects o, 
       titlement_values e 
SET e.customattribute12 = (
    SELECT GROUP_CONCAT(DISTINCT o.minvalue) 
    WHERE f.ldkey = o.ld_key 
      AND o.TITLE_VALUEKEY = e.TITLE_VALUEKEY 
      AND e.TITLE_VALUE = 'ZD%' 
      AND f.ldkey = 13 
      AND e.TITLEMENTTYPEKEY = 13 
    GROUP BY e.title_value)

I tried to execute the below query as well but no luck in it as well:

UPDATE firstEntry f, 
       objects o, 
       titlement_values e 
SET e.customattribute12 = minval 
FROM (SELECT GROUP_CONCAT(DISTINCT o.minvalue) AS minval, 
             e.title_value 
      WHERE f.ldkey = o.ld_key 
        AND o.TITLE_VALUEKEY = e.TITLE_VALUEKEY 
        AND e.TITLE_VALUE = 'ZD%' 
        AND f.ldkey = 13 
        AND e.TITLEMENTTYPEKEY = 13 
      GROUP BY e.title_value)

Here is the table result of using select statement on joining the 3 tables mentioned in the query

group_concat	e.titlement_value
A1,A2,A3	Zd_A
A1,B2	Zd_B

Now i need to take the value of this group_concat and update it in the column e.customattribute12 as shown

e.titlement_value	e.customattribute12
zd_A	A1,A2,A3
zd_B	A1,B2

Answer 1

Once you are able to make sure that the subquery works correctly, try applying the join between your table to be updated and your crafted subquery, on matching "title_value" values.

UPDATE titlement_values
INNER JOIN (SELECT GROUP_CONCAT(DISTINCT o.minvalue) AS minval, 
                   e.title_value 
            FROM       firstEntry       f
            INNER JOIN objects          o ON f.ldkey = o.ld_key
            INNER JOIN titlement_values e ON o.TITLE_VALUEKEY = e.TITLE_VALUEKEY 
            WHERE e.TITLE_VALUE LIKE 'ZD%' 
              AND f.ldkey = 13 
              AND e.TITLEMENTTYPEKEY = 13 ) cte
        ON titlement_values.title_value = cte.title_value
SET customattribute12 = cte.minval