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

Question

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.

• danblack: Is Elizabeth meant to have an id of 3? I can't see affected rows returning non-0 - at least in SQL. I'm not sure what you are asking. Here's a fiddle of how I see your problem described and I don't see anything odd. Maybe something at the PHP layer?
• danblack: mariadb-10.2.6 is the first stable release of the 10.2 series and has since had 25 further compatible releases of bug fixes. I haven't checked all release notes to see if it fixes this however a version upgrade may be prudent. 10.2.X really not a 5.5.5 equalivant - that's just a number that used to trick replication with MySQL into working.
• John: @danblack I'm having some oddities, some queries work and some don't. I just updated MariaDB to the latest stable and I'll get back here in the morning. I think the problem was in a serialized encode something or - it's late, mostly a note for myself. I'll edit the post with more full blown code if it's not related to an update to MariaDB. 🙂︎
• Akina: ODKU is performed by some unique constraint violation. Reassigning the values of the fields which causes ODKU execution makes no sense except DO NOTHING emulation (but INSERT IGNORE is more simple in such case).
• Rick James: You have two rows with id=2. If that is a typo, please fix it. If it is deliberate, please explain the intent.

Answer 1

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);

• Rick James: What column is missing? Why does it matter? Please either make the question and answer fit together for the benefit of other readers. (Else delete the Question.)
• ypercubeᵀᴹ: The first one will surely work as well. The only difference is that it will update a, c and d but not column b.
• John: @ypercubeTM Perhaps the one column that I needed to UPDATE was the one I did not have? I don't recall though it doesn't change the fact that both you and I are simultaneously correct. :-)