CODEWITHSUNDEEP
mysqlsql
Andrius Bentkus
Andrius Bentkus

Reputation: 1392

Insert and select the id of a unique element in one query

I have a simple table like this

CREATE TABLE authid(
  id      INT NOT NULL AUTO_INCREMENT,
  authid  VARCHAR(128) NOT NULL UNIQUE,
  PRIMARY KEY(id)
);

Now if I insert a value with

INSERT INTO authid(authid) VALUES('test');

It will work fine and return the inserted id the first time, but if I do it again when the authid already exists (notice that we have authid marked as UNIQUE) it will return an error.

Is there a way achieve this this in one SQL statement: Insert it, get the id and if it already exists, still get the id.

Upvotes: 0

Views: 297

Answers (3)

Andriy M
Andriy M

Reputation: 77697

Insert the value conditionally (i.e. if it doesn't exist). Whether the insert takes place or not, by the end of the statement the result will be the same: the value will be in the table. So, just select the ID of the row that matches that value. Or, speaking in SQL, like this:

INSERT INTO authid (authid)
SELECT 'test'
WHERE NOT EXISTS (
    SELECT *
    FROM authid
    WHERE authid = 'test'
);

SELECT id
FROM authid
WHERE authid = 'test'
;

Upvotes: 0

Adam Plocher
Adam Plocher

Reputation: 14233

Take a look at this: http://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html

If you're using MySQL 5.0 or higher you can use the "INSERT ... ON DUPLICATE KEY UPDATE" syntax. You may be able to combine that with LAST_INSERT_ID() (I'm not positive about that)

So:

insert into authid (authid) values ('test') on duplicate key update id=LAST_INSERT_ID(id), authid='test';
select LAST_INSERT_ID();

Upvotes: 1

Laurent S.
Laurent S.

Reputation: 6947

Well indeed if you try to insert 2 times the same value in a UNIQUE field, it won't work, that's the point of UNIQUE fields.

If I understand well, you want to know if it's possible whether to use an INSERT or an UPDATE statement depending on the existance of an item or not ? Then you need 2 queries, 1 to test existence, the other to insert new value or update existing one

Upvotes: 0

Related Questions