6

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

Community
  • 1
  • 1
Ted
  • 3,805
  • 14
  • 56
  • 98
  • 1
    Buried [in the documentation](http://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html) - a new insert will return 1 affected row, while an update will return 2. `INSERT IGNORE` will return 0 if ignored, I believe. – Michael Berkowski Dec 04 '12 at 01:42

1 Answers1

4

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.

Bohemian
  • 412,405
  • 93
  • 575
  • 722