Menu
I am using PHP and MySQL.
If I use an INSERT ON DUPLICATE UPDATE SQL statement, then how do I know if the last operation was an successful insert and not an update or unsuccessful insert?
The assumptions are the table in question does not use an auto increment, so I can't use a mysql_insert_id to help me find out.
518
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.
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.
Insert is for putting in a fresh record to the table. while the update enables you to modify the inserted record e.g. modifying data type etc.
The UPSERT is an atomic operation that means it is an operation that completes in a single-step. For example, if a record is new, it will trigger an INSERT command. But, if it already exists in the table, then this operation will perform an UPDATE statement.
You'll want to check mysql_affected_rows() which will return 1 on an insert and 2 on an update. As per the mysql documentation.
if (mysql_affected_rows() == 1) //INSERT
elseif (mysql_affected_row() == 2) //UPDATE
Unless the update does not change anything, in which case it will return 0.
152
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
