Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

INSERT IGNORE - How can I tell if inserted?

Tags:

sql

mysql

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

like image 863
Ted Avatar asked Oct 06 '22 18:10

Ted


1 Answers

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.

like image 155
Bohemian Avatar answered Oct 10 '22 01:10

Bohemian