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.
namerows may have their values change, I'm trying to keep the code simple.
idis the primary key and there are no other keys.
UPDATEhas 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
- There is no clarification in regards to
VALUES()if it is using the
$row(e.g. from PHP) data to
UPDATEif 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.