I am working in PHP.
Please what's the proper way of inserting new records into the DB, which has unique field.
I am inserting lot of records in a batch and I just want the new ones to be inserted and I don't want any error for the duplicate entry.
Is there only way to first make a SELECT and to see if the entry is already there before the INSERT – and to INSERT only when SELECT returns no records? I hope not.
I would like to somehow tell MySQL to ignore these inserts without any error.
Thank you
Best Answer
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
andvalue
. There is one entry, id=1 and value=1. If you run the following statements:You still have one record, with id=1 value=50. Note that the whole record was DELETED first however, and then re-inserted. Then:
The operation executes successfully, but nothing is inserted. You still have id=1 and value=50. Finally:
You now have a single record with id=1 and value=200.