CODEWITHSUNDEEP
mysql
Lu Blue
Lu Blue

Reputation: 355

Update MySQL table using JOIN and GROUP BY

I am trying to update a table by joining with two other tables, but I need to group by the ID of the updated table, something like :

UPDATE T1
SET T1.AForeignID = T3.ID
FROM TableOne T1 
LEFT JOIN TableTwo T2 ON T2.ID = T1.BForeignID 
LEFT JOIN TableThree T3 ON T3.Code = T2.Code AND T3.ACode  = T1.ACode 
GROUP BY T1.ID

But I cant use GROUP BY , it shows an error.

Upvotes: 0

Views: 30

Answers (1)

ScaisEdge
ScaisEdge

Reputation: 133360

You can't use group by without an aggregation function (min(), max(), count().

the GROUP BY clause define the columns which are used to perform an aggregation

UPDATE T1
FROM TableOne T1 
LEFT JOIN TableTwo T2 ON T2.ID = T1.BForeignID 
LEFT JOIN TableThree T3 ON T3.Code = T2.Code AND T3.ACode  = T1.ACode 
SET T1.AForeignID = T3.ID

and the update with join should use an UPDATE .. FROM .. JOIN syntax

..

Upvotes: 1

Related Questions