CODEWITHSUNDEEP
mysql
AAA
AAA

Reputation: 2450

Update column with alphabetical list from a different select statement

I am looking to do a SQL update statement that seems to have some complexities.

I have an inventory table of cars, and it looks like this:

Inventory

CarID Car Color
1 Ford Blue
2 Ford Red
3 Ford Green
4 Chevy Blue
5 Chevy Black
6 Chevy White

I am trying to UPDATE a summary table that looks like this (the update statement will run whenever the Inventory table gets updated):

Summary

CarSummaryID Car Colors
1 Ford Blue, Green, Red
2 Chevy Black, Blue, White

How do I write an Update statement which will popular the Summary table for each car with the corresponding colors and put the colors in alphabetical order?

Thank you

Upvotes: 1

Views: 35

Answers (1)

Bill Karwin
Bill Karwin

Reputation: 562731

If you can add a UNIQUE KEY to Summary.Car, you can use INSERT...ON DUPLICATE KEY UPDATE this way:

INSERT INTO Summary (Car, Colors)
SELECT Car, GROUP_CONCAT(DISTINCT Color ORDER BY Color) AS Colors
FROM Inventory
GROUP BY Car
ON DUPLICATE KEY UPDATE Colors = VALUES(Colors);

Or on MySQL 8.0:

INSERT INTO Summary (Car, Colors)
SELECT Car, GROUP_CONCAT(DISTINCT Color ORDER BY Color) AS NewColors
FROM Inventory
GROUP BY Car
ON DUPLICATE KEY UPDATE Colors = NewColors;

If you can't make the UNIQUE KEY, then IODKU won't work. It only updates rows if there's a unique key conflict with an existing row.

In that case, use a multi-table UPDATE:

UPDATE Summary JOIN (
  SELECT Car, GROUP_CONCAT(DISTINCT Color ORDER BY Color) AS NewColors
  FROM Inventory
  GROUP BY Car
) AS t USING (Car)
SET Summary.Colors = t.NewColors;

But that will only update existing rows in the Summary table, it can't create new rows.

If you want it to happen automatically as data changes in Inventory, the comment from blabla_bingo is a good one. A trigger would help do that. I haven't tested the following, but something like this may get you started:

CREATE TRIGGER InvSummaryIns AFTER INSERT ON Inventory
FOR EACH ROW
    INSERT INTO Summary (Car, Colors)
    SELECT Car, GROUP_CONCAT(DISTINCT Color ORDER BY Color) AS NewColors
    FROM Inventory
    WHERE Car = NEW.Car
    GROUP BY Car
    ON DUPLICATE KEY UPDATE Colors = NewColors;

The WHERE clause is because you would only need to update the row for the respective Car value, not the whole table of all car types.

You would also need similar triggers AFTER UPDATE and AFTER DELETE, because those also change the inventories.

Upvotes: 2

Related Questions