Menu
I have a statement that tries to insert a record and if it already exists, it simply updates the record.
INSERT INTO temptable (col1,col2,col3)
VALUES (1,2,3)
ON DUPLICATE KEY UPDATE col1=VALUES(col1), col2=VALUES(col2), col3=VALUES(col3);
The full statement has multiple inserts and I'm looking to count number of INSERTs against the UPDATEs. Can I do this with MySQL variables, I've yet to find a way to do this after searching.
324
By definition, atomicity requires that each transaction is an all or nothing. So yes it is atomic in the sense that if the data that you are trying to insert will cause a duplicate in the primary key or in the unique index, the statement will instead perform an update and not error out.
INSERT ... ON DUPLICATE KEY UPDATE is a MariaDB/MySQL extension to the INSERT statement that, if it finds a duplicate unique or primary key, will instead perform an UPDATE. The row/s affected value is reported as 1 if a row is inserted, and 2 if a row is updated, unless the API's CLIENT_FOUND_ROWS flag is set.
ON DUPLICATE KEY UPDATE inserts or updates a row, the LAST_INSERT_ID() function returns the AUTO_INCREMENT value. The ON DUPLICATE KEY UPDATE clause can contain multiple column assignments, separated by commas. The use of VALUES() to refer to the new row and columns is deprecated beginning with MySQL 8.0.
A better question might be what source is faster: using the application code (like java or C#) to execute the statement, or using a PL/SQL procedure (or function) to carry it out. insert is more faster than update ... because in insert there no checking of data..
From Mysql Docs
In the case of "INSERT ... ON DUPLICATE KEY UPDATE" queries, the return value will be 1 if an insert was performed, or 2 for an update of an existing row.
Use mysql_affected_rows() after your query, if INSERT was performed it will give you 1 and if UPDATE was performed it will give you 2.
170
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
