MySQL INSERT IF NOT EXISTS: Checking whether a record exists and inserting

MySQL Insert If Not Exists

Why doesn’t MySQL Insert If Not exists work?????

UPDATE: CHECK AT THE BOTTOM OF POST

Bogdan has the answers .

First of all, this information relates solely to MySQL and may not apply to MSSQL or PostgreSQL.

Have you been trying to insert records into a table based on whether there is already a record there of not. Trying multiple SQL queries of the form:

INSERT INTO list
(code, name, place)
SELECT *
FROM
(SELECT ('ABC', 'abc ', 'Johannesburg',)) AS tmp
WHERE NOT EXISTS 
(
SELECT * FROM sharelist WHERE code = 'ABC'
);

Some people also use:

INSERT INTO table (fields) VALUES (values) ON DUPLICATE KEY UPDATE ID=ID;

Provided you have a unique primary key. This is not the correct way of using a Mysql Insert if not Exists…

Well Bogdan has the answers.

There is syntax used to tackle this exact problem in an efficient way and that is:

INSERT IGNORE INTO `list`
`code` = 'ABC',
`name` = 'abc',
`place` = 'Johannesburg',
;

It’s that easy.

If the record exists it will be ignored (silently skipped). If it does not it will be inserted.

Warm and Easy.

Update: No maybe not so easy. Damn you Mysql Insert if not Exists

The problem arises when you have a field (most likely an id) which is not added by you. So an auto incremented field. The above code will not skip the insert and will always insert the record in as it is unique because of the auto increment field.

So if you have an auto increment use the following:

INSERT INTO list (code, name, address,)
SELECT 'ABC', 'ABC name', 'Johannesburg'
FROM list
WHERE NOT EXISTS(
    SELECT code, name, address
    FROM list
    WHERE code = 'ABC'
	AND name = 'ABC name'
	AND address = 'Johannesburg'
)
LIMIT 1

That will do the job. However, one important point is that the table cannot be empty it must have at least one field so that is unfortunately a flaw.

Who thought a Mysql insert if not Exists would be this (easy) hard.

Read this question on stack: prevent autoincrement on MYSQL duplicate insert

Original article found on this blog: Mr.TimRosenblatt

If someone has a better idea please contact me: ME or add a comment.

Thanks