Shaun Perry Shaun Perry - 2 years ago 95
SQL Question

Getting count of insert/update rows from ON DUPLICATE KEY UPDATE

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.



Answer Source

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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download