• support@answerspoint.com

How to 'insert if not exists' in MySQL?

3177

I started by googling, and found this article which talks about mutex tables.

I have a table with ~14 million records. If I want to add more data in the same format, is there a way to ensure the record I want to insert does not already exist without using a pair of queries (ie, one query to check and one to insert is the result set is empty)?

Does a unique constraint on a field guarantee the insert will fail if it's already there?

It seems that with merely a constraint, when I issue the insert via php, the script croaks.

1Answer


0
INSERT INTO `table` (value1, value2) 
SELECT 'stuff for value1', 'stuff for value2' FROM `table` 
WHERE NOT EXISTS (SELECT * FROM `table` 
      WHERE value1='stuff for value1' AND value2='stuff for value2') 
LIMIT 1 

Alternatively, the outer SELECT statement can refer to DUAL in order to handle the case where the table is initially empty:

INSERT INTO `table` (value1, value2) 
SELECT 'stuff for value1', 'stuff for value2' FROM DUAL
WHERE NOT EXISTS (SELECT * FROM `table` 
      WHERE value1='stuff for value1' AND value2='stuff for value2') 
LIMIT 1 
  • answered 8 years ago
  • Sunny Solu

Your Answer

    Facebook Share        
       
  • asked 9 years ago
  • viewed 3177 times
  • active 9 years ago

Best Rated Questions