Using group_concat with update statement in MySql-CodePudding

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

CodePudding user response：

You need to rewrite your update as MySQL doesn't support FROM clauses in UPDATE statements: a correlated subquery will do the trick, as it returns only one scalar value.

Also JOIN operations are around for 30 years and are established standard, you should switch also to it.

with the problem you mentioned in the comment.

mjava.sql.BatchUpdateException: You can't specify target table 'e' for update in FROM claus

it os MySQL error 1063

MySQL doesn't like when the updated table is in somewhere used again, we can avoid that with a trick seen below, as we force MySql to produce a new temporary table

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

CodePudding user response：

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