MySQL Insert If Not Exists

INSERT IGNORE is the syntax for something equivalent for MySQL INSERT IF NOT EXISTS. There is really no direct command matching the SQL Server version as outlined in this articles title, at least not in the current release of MySql.

The statement INSERT IGNORE and to some extent REPLACE INTO, do essentially the same thing, inserting a record if that given record does not exists.

MySQL insert if not exists

Let’s consider the following samples, where we want a record inserted if it’s not there already and ignoring the insert if it exists, vs updating the record if it exists in the second example.

Insert only if that record does not already exist

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.

Insert if the record does not exist, otherwise update record

REPLACE INTO `table`
 SET 'column_1` = `value_1`,
 `column_2` = `value_2';

MySQL provides a lot of flexibility, but sometimes we have to be creative and research solutions, as some commands and functions names are not that obvious.
References: INSERT IGNORE and REPLACE INTO

Listed in Linux, MySQL, Web Development

Tags: insert if not exists, lamp, MySQL, replace into

6 responses to “MySQL Insert If Not Exists”

1. Charles says:

   June 3, 2009 at 1:18 pm

   Awesome, been looking for a way to achieve this for ages.

   Thanks!

   Reply
2. Ales says:

   July 10, 2009 at 8:14 am

   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

   Reply
3. Danny Tsang says:

   December 6, 2009 at 5:51 am

   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.

   Reply
4. Dudewtf says:

   January 30, 2010 at 3:45 am

   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 ?

   Reply
5. server says:

   June 11, 2010 at 11:35 am

   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

   Reply
6. Tom says:

   August 7, 2014 at 1:56 am

   I know this is old, but it popped up in a Google search, so I’ll just issue a WARNING anyway;

   REPLACE INTO will DELETE the record if it exists and then INSERT new record. It’s nifty if that’s what you want to do, but beware, that if you have foreign keys with ON DELETE CASCADE pointing to this record, those WILL ALSO BE DELETED!

   In the example in this thread, you’d be MUCH better off using an:

   INSERT INTO … ON DUPLICATE KEY UPDATE …

   This will insert a new record, if the key does not exist, and update the existing record, if the key DOES exist.

   Reply