MySQL INSERT only if the row does not exist, otherwise select a duplicate row

Question

I am looking for an instruction that will try to insert a row into a table, but return the primary key of the duplicated row if it occurs. One field in the table is an automatically incrementing primary key, the other is unique.

Answer 1

This should, at least theoretically, work for you:

First expand the table to have an extra tinyint type column layout. 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 assume that column a has a unique index and there is a row with a = 1.)

Then you can get the identifier of a new row (in case of INSERT) or an existing row (in case of UPDATE) through

 SELECT LAST_INSERT_ID() 

Answer 2

This should work in DB2, I don’t know if it will be in MySQL or if there is a 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 no unique value, inserting a new value and returning the generated primary key from the table. Then it is combined with a selection that returns the corresponding key if unique values ​​are already specified in the table. Only one of these operators should return a row, the second column indicates whether the primary key is new or existing.