Home MYSQL – join 2 tables, match multiple columns – update value from other table where multiple values match

Questions

MYSQL – join 2 tables, match multiple columns – update value from other table where multiple values match

January 18, 2022

I need to join two tables (TableCorrected) with (TableOriginal). Both tables have columns for ID-number and Articlenumber. Both tables also have a column named "Quantity".

I want to join the two tables, match ID-number AND Articlenumber and update the Quantity-value from "TableOriginal" to "TableCorrected" ONLY where ID-number as well as Articlenumber matches.

I’ve started a statement as below – but I’m sure it’s not correct, returns 0 result.

UPDATE TableOriginal
INNER JOIN TableCorrected ON TableOriginal.ID = TableCorrected.ID
SET TableOriginal.Quantity = TableCorrected.Quantity
WHERE TableCorrected.ID = TableOriginal.ID 
  AND TableCorrected.Article = TableOriginal.Article

>Solution :

but I’m sure it’s not correct

It is correct. But not optimal. More clear is, for example,

UPDATE TableOriginal
INNER JOIN TableCorrected USING (ID, Article)
SET TableOriginal.Quantity = TableCorrected.Quantity;

UPDATE TableOriginal
INNER JOIN TableCorrected ON TableOriginal.ID      = TableCorrected.ID
                         AND TableOriginal.Article = TableCorrected.Article
SET TableOriginal.Quantity = TableCorrected.Quantity;