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

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

CodePudding user response：

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;

returns 0 result.

UPDATE does not return rows. Rather than SELECT.