Timeline for INSERT ON DUPLICATE KEY UPDATE to UPDATE multiple rows in single query
Current License: CC BY-SA 4.0
14 events
| when toggle format | what | by | license | comment | |
|---|---|---|---|---|---|
| Feb 8, 2020 at 22:36 | comment | added | Rick James |
I ran your test on multiple versions -- all gave rows affected as 6 (3 match + 3 change), and SELECT * gave the desired 4,5,6.
|
|
| Feb 8, 2020 at 22:15 | comment | added | Rick James |
@John - Ah, yes; that is confusing. Here is what I got for a 10.2 instance for @@version: 5.5.5-10.2.30-MariaDB-1:10.2.30+maria~bionic. The useful info is 10.2.30.
|
|
| Feb 8, 2020 at 18:05 | vote | accept | John | ||
| Feb 8, 2020 at 18:02 | comment | added | John | @RickJames That is simply what MariaDB's version function returns. | |
| Feb 8, 2020 at 2:24 | comment | added | Rick James | 10.2 is approximately 5.7, two major releases after 5.5. | |
| Feb 8, 2020 at 2:09 | comment | added | John | @RickJames Typo, was trying to keep the example simple as the code in the system is a bit more complex. I managed to answer my own question though and make notes in my references. | |
| Feb 8, 2020 at 2:08 | history | edited | John | CC BY-SA 4.0 |
edited body
|
| Feb 8, 2020 at 2:08 | comment | added | 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.
|
|
| Feb 5, 2020 at 17:45 | answer | added | John | timeline score: -1 | |
| Feb 5, 2020 at 6:11 | comment | added | 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). | |
| Feb 5, 2020 at 2:47 | comment | added | 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. 🙂︎ | |
| Feb 5, 2020 at 0:20 | comment | added | 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. | |
| Feb 5, 2020 at 0:16 | comment | added | 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?
|
|
| Feb 4, 2020 at 23:13 | history | asked | John | CC BY-SA 4.0 |