Mysql – INSERT ON DUPLICATE KEY UPDATE to UPDATE multiple rows in single query

insertmariadbMySQLupdate

I've come across people recommending using ON DUPLICATE KEY UPDATE to UPDATE multiple rows using a single query. Unfortunately no one seems to be using clean SQL (defining an obvious primary key of id and using at least two rows) and so I have not been able to confirm the effect to a satisfactory degree.

Presume all the data in the table exists and that some of the quantity rows will be different:

INSERT INTO variations (id, name, quantity) 
VALUES 
(1, 'John', 4), 
(2, 'Amy', 5), 
(3, 'Elizabeth', 6) 
ON DUPLICATE KEY UPDATE 
id=VALUES(id), 
name=VALUES(name), 
quantity=VALUES(quantity);

If the quantities in the table are 1, 2 and 3 then what I've constituted is that the above query should UPDATE them to 4, 5 and 6. However manually running the command returns Affected rows: 0 and the data in my application is beyond any doubt not changing.

Clarifications:

  • The name rows may have their values change, I'm trying to keep the code simple.
  • The id is the primary key and there are no other keys.
  • No UPDATE has to happen if the all of the data for all of the row are the same between the query and table row.
  • Sometimes only one column of data might change, sometimes all columns except id might change.
  • There is no clarification in regards to VALUES() if it is using the $row (e.g. from PHP) data to UPDATE if it differs from what is already in the row.
  • There is no clarification in regards to if all the columns must be specified (besides the the primary or a unique key).
  • The documentation at both MariaDB and MySQL leaves a nebulous mystical cloud of unknowns.
  • I'm running MariaDB 10.2.6 (MySQL 5.5.5).

I'm tempted just to dump the code in to a loop to run an UPDATE query per row as I don't have time to create a huge project out of this. None of the existing threads here or at SO properly address the multiple UPDATE single query issue (examples of using case look like monstrous nightmares to construct) and so I'm hoping for an answer that can clarify this beyond any doubt so I can learn from this frustrating challenge.

Best Answer

Thanks to #danblack and dbfiddle.uk in short I determined that I was missing one of the columns in the ON DUPLICATE KEY UPDATE.

Every single column must be defined for ON DUPLICATE KEY UPDATE to work!

I effective had columns id, a, b, c and d in the table. I forgot to define one of the columns (that wasn't id as that is the first half of the requirement).

Won't Work (missing column):

ON DUPLICATE KEY UPDATE 
id=VALUES(id), 
a=VALUES(a),
c=VALUES(c),
d=VALUES(d);

Will Work (has all table columns):

ON DUPLICATE KEY UPDATE 
id=VALUES(id), 
a=VALUES(a),
b=VALUES(b),
c=VALUES(c),
d=VALUES(d);
Related Question