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
Inspect the number of rows affected:
1
means an INSERT
occurred2
means an UPDATE
occurredIf 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.
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