MySQL - ignore insert error: duplicate entry
You can use INSERT... IGNORE syntax if you want to take no action when there's a duplicate record.
You can use REPLACE INTO syntax if you want to overwrite an old record with a new one with the same key.
Or, you can use INSERT... ON DUPLICATE KEY UPDATE syntax if you want to perform an update to the record instead when you encounter a duplicate.
Edit: Thought I'd add some examples.
Examples
Say you have a table named tbl
with two columns, id
and value
. There is one entry, id=1 and value=1. If you run the following statements:
REPLACE INTO tbl VALUES(1,50);
You still have one record, with id=1 value=50. Note that the whole record was DELETED first however, and then re-inserted. Then:
INSERT IGNORE INTO tbl VALUES (1,10);
The operation executes successfully, but nothing is inserted. You still have id=1 and value=50. Finally:
INSERT INTO tbl VALUES (1,200) ON DUPLICATE KEY UPDATE value=200;
You now have a single record with id=1 and value=200.
You can make sure that you do not insert duplicate information by using the EXISTS condition.
For example, if you had a table named clients with a primary key of client_id, you could use the following statement:
INSERT INTO clients(client_id, client_name, client_type)SELECT supplier_id, supplier_name, 'advertising'FROM suppliersWHERE not exists (select * from clientswhere clients.client_id = suppliers.supplier_id);
This statement inserts multiple records with a subselect.
If you wanted to insert a single record, you could use the following statement:
INSERT INTO clients(client_id, client_name, client_type)SELECT 10345, 'IBM', 'advertising'FROM dualWHERE not exists (select * from clientswhere clients.client_id = 10345);
The use of the dual table allows you to enter your values in a select statement, even though the values are not currently stored in a table.