INSERT IGNORE - How can I tell if inserted?
mysql, sql
Solution
Inspect the number of rows affected:
- A return value of `1` means an `INSERT` occurred
- A return value of `2` means an `UPDATE` occurred
If you're using JDBC, this is the `int` return value of the `update()` or `execute()` call.
All frameworks have a way of passing this value back to you, for example Hibernate's `Query.executeUpdate()` returns this `int` value.
Problem
I am going to need an SQL query that would `INSERT .. ON DUPLICATE KEY UPDATE` (or `INSERT IGNORE INTO`) my table. I need to know if an update actually took place or rather a new row was inserted. A good reference was this SO answer, unfortunately there's no answer to the action taken. As for now I am using INSERT .. ON DUPLICATE KEY UPDATE: ``` INSERT INTO `tbl` (`CLIENT_ID`, `COMP_ID`, `DATETIME`) VALUES (12334,32,NOW()) ON DUPLICATE KEY UPDATE `COMP_ID`=VALUES(`COMP_ID`), `DATETIME`=VALUES(`DATETIME`);"; ``` and check the affected_rows value. If affected_rows equals 1, means -> new row inserted If affected_rows equals 2, means -> row updated I'd like no change to happen in case of a duplicate, but I would like to know a duplicate exists. Using MYSQL