Menu
Using PDO / PHP and MySQL, how can I check if a record was inserted or updated when I use an INSERT ON DUPLICATE KEY UPDATE statement?
I have seen a solution using mysql_affected_rows() for PHP, but I am looking for a way I can use with PDO.
748
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.
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.
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.
The Insert on Duplicate Key Update statement is the extension of the INSERT statement in MySQL. When we specify the ON DUPLICATE KEY UPDATE clause in a SQL statement and a row would cause duplicate error value in a UNIQUE or PRIMARY KEY index column, then updation of the existing row occurs.
If you use PDO::exec() the return value will be 1 if the row has been inserted and 2 if the row has been updated.
If you're using a prepared statement and PDOStatement::execute() the same is true for PDOStatement::rowCount()
57
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
