CODEWITHSUNDEEP
mysqlsql
wav
wav

Reputation: 1552

Insert a record only if it is not present

I need to create a query to insert some records, the record must be unique. If it exists I need the recorded ID else if it doesnt exist I want insert it and get the new ID. I wrote that query but it doesnt work.

SELECT id FROM tags WHERE slug = 'category_x'
WHERE NO EXISTS (INSERT INTO tags('name', 'slug') VALUES('Category X','category_x'));

Upvotes: 0

Views: 97

Answers (3)

Jason Heo
Jason Heo

Reputation: 10236

MySQL has nice REPLACE. It is easy to use and remember it's syntax as same as INSERT. in you case, just run following query.

REPLACE INTO tags('name', 'slug') VALUES('Category X','category_x')

It acts like INSERT when no unique constraint violation. If duplicated value found on PK or UNIQUE key, then other columns will be UPDATED with given values. It is done by DELETE duplicated record and INSERT new record.

Upvotes: 0

Shiva
Shiva

Reputation: 20935

It's called UPSERT (i.e. UPdate or inSERT).

INSERT INTO tags
('name', 'slug')
VALUES('Category X','category_x')
ON DUPLICATE KEY UPDATE
'slug' = 'category_x'

MySql Reference: 13.2.5.3. INSERT ... ON DUPLICATE KEY UPDATE Syntax

Upvotes: 2

user2989408
user2989408

Reputation: 3137

Try something like...

IF (NOT EXISTS (SELECT id FROM tags WHERE slug = 'category_x'))
BEGIN
    INSERT INTO tags('name', 'slug') VALUES('Category X','category_x');
END
ELSE 
BEGIN
    SELECT id FROM tags WHERE slug = 'category_x'
END

But you can leave the ELSE part and SELECT the id, this way the query will always return the id, irrespective of the insert...

Upvotes: 1

Related Questions