CODEWITHSUNDEEP
mysql
soapergem
soapergem

Reputation: 9989

How can I update each grouped row using GROUP BY and JOIN in MySQL?

I've created a SQL Fiddle to illustrate what I want to do here. I have a table called "items" which contains both individual information along with aggregated information. There is a column called "keycode" which represents an individual piece of data for that item. There is a column called "promo" which is used to group things together.

I also have another column called "keycodes" (note the S) which I want to have contain a comma-separated list of keycodes for the same promo.

So for instance, if there are three rows with promo "A", and their individual values for the "keycode" column are ABC, DEF, and GHI, then I would all three of those rows to have the same value for the "keycodes" column. Namely: ABC,DEF,GHI

I tried doing this with an UPDATE statement; however, this only updates one of the grouped rows:

UPDATE items i 
INNER JOIN (
  SELECT keycode, GROUP_CONCAT(keycode SEPARATOR ',') AS keycodes 
  FROM items 
  WHERE promo IS NOT NULL
  GROUP BY promo
) AS t ON i.keycode = t.keycode 
SET i.keycodes = t.keycodes;

So in my SQL Fiddle, the rows with IDs 1 and 4 were correctly updated (and technically 5 too, but that's only because it only had one value). However the rows with IDs 2, 3, and 6 do not have the values I want.

Is this something that can be accomplished in MySQL?

Desired output:

+----+---------+-------------+-------+
| id | keycode | keycodes    | promo |
+----+---------+-------------+-------+
|  1 | ABC     | ABC,DEF,GHI | A     |
|  2 | GHI     | ABC,DEF,GHI | A     |
|  3 | DEF     | ABC,DEF,GHI | A     |
|  4 | QRS     | QRS,TUV     | B     |
|  5 | WXY     | WXY         | C     |
|  6 | TUV     | QRS,TUV     | B     |
+----+---------+-------------+-------+

Upvotes: 0

Views: 52

Answers (1)

Ankit Bajpai
Ankit Bajpai

Reputation: 13509

Use this update query to achieve desired result:-

UPDATE items i 
INNER JOIN (
           SELECT keycode, GROUP_CONCAT(keycode SEPARATOR ',') AS keycodes, promo
           FROM items 
           WHERE promo IS NOT NULL
           GROUP BY promo
           ) AS t ON i.promo = t.promo
SET i.keycodes = t.keycodes;

Upvotes: 1

Related Questions