MySQL INSERT INTO, but ON DUPLICATE return it's id

Question

First of all i'm very confused that i cannot find the solution to this simple question myself.

What i'm trying to accomplish is the following.

Within a Transaction i want to INSERT INTO as long as a unique index (based on two fields) doesn't exist. So for example i want a first and a last name to be unique together. But i want the first name to be used multiple times and also the last name, but not as a pair.

So far this works for me, but when i add a duplicate value, i don't want anything to be updated. I simply want to retrieve the id of this field, to be used in the next part of the transaction.

In my case i'm using it in a NodeJS environment, but that should not matter.

So please i would like to get an example query to 'insert if not exists' but if it does exist i would like to get the 'id' so i can use that in the next transaction.

Thanks in advance

Answer 1

You are not going to find a single query that accomplishes all this. I would issue a plain insert statement. If it returns with duplicate key error to the application logic, then you can query the id of the "offending" record with a subsequent select.

Obviously, you can encapsulate the logic into a stored procedure that follows the above described steps and appear as a single query to the calling application.