What is the difference between INSERT IGNORE and INSERT ON DUPLICATE KEY UPDATE in SQL?What is the difference between INSERT IGNORE and INSERT ON DUPLICATE KEY UPDATE in SQL?
INSERT IGNORE
– If a duplicate key error occurs, the query ignores the insertion and continues executing.
– No error is thrown, but the affected rows count will be 0.
– The existing record remains unchanged.
– Useful when you want to avoid duplicate key errors and don’t need to update existing records.
INSERT … ON DUPLICATE KEY UPDATE
– If a duplicate key is encountered, the query updates the existing record with the new values.
– If no duplicate key is found, the query inserts a new record.
– Returns the number of affected rows (inserted or updated).
– Useful when you want to either insert a new record or update an existing one based on the presence of the key.
Example:
Suppose we have a table `users` with columns `id` (primary key), `name`, and `email`.
CREATE TABLE users (
id INT PRIMARY KEY,
name VARCHAR(255),
email VARCHAR(255)
Here’s the difference:
INSERT IGNORE
– Ignores duplicate key errors
– Doesn’t insert or update existing records
– Returns no error, but affected rows = 0
INSERT … ON DUPLICATE KEY UPDATE
– Updates existing records with new values on duplicate key
– Inserts new records if no duplicate key is found
– Returns the number of affected rows (inserted or updated)