MySQL INSERT only if row doesn't exist, otherwise select the duplicate row.

Question

I am looking for a statement that will try to insert a row into a table, but return the primary key of the duplicate row if one is encountered. One field in the table is the auto incrementing primary key, the other is unique.

Answer 1

This should work in DB2, dont know if it will in MySQL or if there is special MySQL syntax for it:

SELECT pk, 'inserted' FROM FINAL TABLE (
  INSERT INTO table (Col1)
  SELECT Val1
  FROM table
  WHERE col1 != Val1
  FETCH FIRST ROW ONLY
)
UNION 
SELECT pk, 'existing'
FROM table
WHERE col1 = val1

The idea here is to select one row from the table when there is not a unique value in there, inserting the new value and returning the generated primary key from the table. This is then combined with the select that returns the corresponding key if the unique values is already in the table. Only one of those statements should return a row, the second column indicating if the primary key is new or exisiting.

Answer 2

This should, at least in theory, work for you:

First extend your table to have an additional column dummy of type tinyint. Then you can use the following query when inserting/updating:

INSERT INTO yourtable (a, b) VALUES (1, 2) ON DUPLICATE KEY UPDATE id  = LAST_INSERT_ID(id), dummy = NOT dummy

(I'm assuming here that the column a has a unique index and a row with a=1 exists.)

You can then get the ID of the new row (in case of an INSERT) or the existing row (in case of an UPDATE) via

SELECT LAST_INSERT_ID()