CODEWITHSUNDEEP
mysqlsqlsql-insert
Abuzer Firdousi
Abuzer Firdousi

Reputation: 1556

mysql: insert record if not exists else return the id of record

i need to insert unique values in a table, and need the ids of records, need to insert those id's in relationship table, need the query to insert the record if not exists return the inset id, if exists return the record primary key(id).

and i want to do this for multiple values, like orange, mango, banana, like batch insert.

schema:

------------
id | tag   |
------------
1  | orange|
------------

i have trid this for a single record

INSERT INTO `tags` (`tag`) 
SELECT 'myvalue1' 
FROM tags
WHERE NOT EXISTS (SELECT 1 FROM `tags` WHERE `tag`='myvalue1') 
LIMIT 1

posted the question to figure out some optimized solution, i don't want to use extra loops in the code to match the values from db.

Upvotes: 23

Views: 21823

Answers (3)

foxybagga
foxybagga

Reputation: 4214

There are 4 methods listed in the below link, please have a look and use whichever suits you.

  • Method 1: SELECT, then INSERT
  • Method 2: try { INSERT } catch { SELECT }
  • Method 3: INSERT IGNORE then SELECT
  • Method 4: INSERT INTO... ON DUPLICATE KEY UPDATE

http://mikefenwick.com/blog/insert-into-database-or-return-id-of-duplicate-row-in-mysql/

Upvotes: 2

Yosra Nagati
Yosra Nagati

Reputation: 790

Here is the mysql state to insert if not exists and return the id either it new insertion or old 

    INSERT INTO `tags` (`tag`) 
SELECT 'myvalue1' 
FROM tags
WHERE NOT EXISTS (SELECT id FROM `tags` WHERE `tag`='myvalue1') 
LIMIT 1;
select * from brand where `tag`='myvalue1'

Upvotes: 3

Marcus Adams
Marcus Adams

Reputation: 53840

There is an example on the documentation page for INSERT ... ON DUPLICATE KEY UPDATE:

Your query would look like this:

INSERT INTO `tags` (`tag`) VALUES ('myvalue1')
  ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id), `tag`='myvalue1';
SELECT LAST_INSERT_ID();

Upvotes: 15

Related Questions