SQL Upsert: INSERT ON DUPLICATE KEY UPDATE and REPLACE

An upsert means “insert this row, or update it if it already exists”. Syncing a product feed, counting page views and saving user settings all need it. MySQL offers three tools, INSERT ... ON DUPLICATE KEY UPDATE, INSERT IGNORE and REPLACE, and they behave very differently when a row already exists.

What counts as a duplicate?

All three tools react to a clash on a PRIMARY KEY or UNIQUE index. Without such a key there is no “existing row” to detect, and every insert simply adds a row.

INSERT … ON DUPLICATE KEY UPDATE

A supplier sends a price list. Product 2 exists (update it), product 10 is new (insert it):

INSERT INTO products (product_id, product_name, category, price, stock)VALUES (2, 'Mechanical Keyboard', 'Electronics', 3299, 60),       (10, 'Webcam HD', 'Electronics', 2499, 40) AS newON DUPLICATE KEY UPDATE  price = new.price,  stock = new.stock; SELECT product_id, product_name, price, stock FROM products WHERE product_id IN (2, 10);

Counters with ON DUPLICATE KEY

A classic use: count events per day without checking first whether the row exists:

CREATE TABLE page_views (page VARCHAR(40) PRIMARY KEY, views INT NOT NULL);INSERT INTO page_views VALUES ('sql-tutorial', 1) ON DUPLICATE KEY UPDATE views = views + 1;INSERT INTO page_views VALUES ('sql-tutorial', 1) ON DUPLICATE KEY UPDATE views = views + 1;INSERT INTO page_views VALUES ('sql-joins', 1)    ON DUPLICATE KEY UPDATE views = views + 1;SELECT * FROM page_views;

INSERT IGNORE

INSERT IGNORE silently skips rows that would cause a duplicate-key error and inserts the rest:

INSERT IGNORE INTO departments (dept_id, dept_name, location)VALUES (1, 'Engineering', 'Chennai'),       (6, 'Legal', 'Delhi');SHOW WARNINGS;SELECT * FROM departments;

REPLACE INTO

REPLACE works like INSERT, but if the key exists it deletes the old row and inserts the new one:

REPLACE INTO customers (customer_id, customer_name, city)VALUES (6, 'Manoj Pillai', 'Kochi'); SELECT * FROM customers WHERE customer_id = 6;

REPLACE fails if other tables reference the row through a foreign key, because it really deletes it. Customer 1 has orders:

REPLACE INTO customers (customer_id, customer_name, city) VALUES (1, 'Ananya Sharma', 'Pune');

ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`company`.`orders`, CONSTRAINT `orders_ibfk_1` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`customer_id`))

Which one should I use?

In other databases the same idea is INSERT ... ON CONFLICT DO UPDATE (PostgreSQL, SQLite) or MERGE (SQL Server, Oracle).

INSERT INTO products (product_id, product_name, category, price, stock)VALUES (3, 'USB-C Hub', 'Electronics', 1999, 30),       (11, 'Sticky Notes', 'Stationery', 99, 500) AS newON DUPLICATE KEY UPDATE stock = new.stock; SELECT product_id, product_name, stock FROM products WHERE product_id IN (3, 11);

Upsert FAQ