CODEWITHSUNDEEP
mysql
Jader Dias
Jader Dias

Reputation: 90485

How to update a table using a select group by in a second one as the data source in MySQL?

I can't do this in MySQL

UPDATE tableA, tableB
SET tableA.column1 = SUM(tableB.column2)
WHERE tableA.column3 = tableB.column4
GROUP BY tableB.column4
;

Neither can I

UPDATE tableA, 
(
  SELECT SUM(tableB.column2) sumB, tableB.column4
  FROM tableB
  GROUP BY tableB.column4
) t1
SET tableA.column1 = sumB
WHERE tableA.column3 = column4
;

Besides it being illegal code, I think you can understand what I tried to do with the queries above. Both of them had the same intent.

How can I do that in MySQL?

Upvotes: 0

Views: 95

Answers (1)

Lukáš Lalinský
Lukáš Lalinský

Reputation: 41306

This would be one way, if you don't mind using a subquery:

UPDATE tableA
SET column1 = (
    SELECT sum(column2)
    FROM tableB
    WHERE tableA.coumn3 = tableB.column4);

Upvotes: 2

Related Questions