How to 'insert if not exists' in MySQL?
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.
cakephp
Mysql
primary-key
sql
- asked 9 years ago
- B Butts
1Answer
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
Best Rated Questions
Your Answer