INSERT IGNORE is the syntax that does mimic INSERT IF NOT EXISTS, as there is no direct command as stated in the title, at least not in the current release of MySql.
The statement INSERT IGNORE (and to some extent REPLACE INTO) does essentially the same thing, inserting a record if that given record does not exists.
See the following samples:
Sample:
INSERT IGNORE INTO `table`
SET ‘column_1` = `value_1`,
`column_2` = `value_2′;
If you’re concerned with existing data that might result in duplicate records, or error out due to duplicate id’s, use REPLACE. MySql will check if the record exists, replace the values given and insert it if the record does not exist.
Sample:
REPLACE INTO `table`
SET ‘column_1` = `value_1`,
`column_2` = `value_2′;
References: INSERT IGNORE and REPLACE INTO
Awesome, been looking for a way to achieve this for ages.
Thanks!
There is another possible solutions – suitable for situation when you cannot use IGNORE
INSERT INTO target_table(fieldname1) SELECT value1 FROM DUAL WHERE (SELECT COUNT(*) FROM target_table WHERE fieldname1=value1)=0;
it took me some time to figure it out but works perfectly
Thanks Ales for the alternative. I couldn’t use the IGNORE statement because of an auto number primary key which made it non distinct on insert.
Hello, does the INSERT IGNORE INTO work when there is no primary key but there might be a column with similar record, for instance a common first name ?
Also i’m using
INSERT INTO `table` (value1,value2) SELECT ‘stuff for value1′,’stuff for value2′ WHERE NOT EXISTS (SELECT * FROM table WHERE value1=’stuff for value1′ AND value2=’stuff for value2′) LIMIT 1