Upserts with ON DUPLICATE KEY UPDATE
40 min · Intermediate
Need help? Ask a tutor on WhatsApp (opens in a new tab)A product feed sends the same items every night: some are new, some only changed price. You want one statement that handles both, without checking first.
The insert-or-update problem
An upsert inserts a row when its key is new and updates the existing row when the key is already there. In MySQL it is one statement, INSERT ... ON DUPLICATE KEY UPDATE.
A plain INSERT of the same key twice fails. The snippet shows the error that an upsert avoids.
CREATE TABLE carts ( customer_id INT, product_id INT, qty INT NOT NULL, PRIMARY KEY (customer_id, product_id));
INSERT INTO carts (customer_id, product_id, qty) VALUES (1, 6, 1);INSERT INTO carts (customer_id, product_id, qty) VALUES (1, 6, 1);ERROR 1062 (23000): Duplicate entry '1-6' for key 'carts.PRIMARY'
Tap "Add to cart" on a phone you already added, and the shop should raise the quantity, not add a second cart line. Attendance marked twice, a price list imported again and a player's runs after each match all work the same way.
A plain INSERT cannot do this. The second insert hits the primary key and fails with error 1062. The two-step fix, "SELECT to check, then INSERT or UPDATE", is longer and has a gap. If two requests check at the same moment, both see "not there", both insert, and one of them crashes. An upsert does the check and the change inside one statement on the server.
INSERT ... ON DUPLICATE KEY UPDATE
ON DUPLICATE KEY UPDATE is the clause that turns an INSERT into an upsert: if the new row would repeat a PRIMARY KEY or UNIQUE value, MySQL updates the existing row instead. ROW_COUNT() is 1 for an insert and 2 for an update.
Example 1 adds to a cart: the first tap inserts, the second raises the quantity. Example 2 loads season runs and Example 3 counts what a price import did.
Example 1 · Add to cart: insert or raise the quantity
CREATE TABLE carts ( customer_id INT, product_id INT, qty INT NOT NULL, PRIMARY KEY (customer_id, product_id));
-- First tap: boAt Airdopes into Asha's cartINSERT INTO carts (customer_id, product_id, qty) VALUES (1, 6, 1) AS newON DUPLICATE KEY UPDATE qty = carts.qty + new.qty;SELECT ROW_COUNT() AS first_tap;
-- Second tap: two more of the same productINSERT INTO carts (customer_id, product_id, qty) VALUES (1, 6, 2) AS newON DUPLICATE KEY UPDATE qty = carts.qty + new.qty;SELECT ROW_COUNT() AS second_tap;
SELECT * FROM carts;+-----------+ | first_tap | +-----------+ | 1 | +-----------+ +------------+ | second_tap | +------------+ | 2 | +------------+ +-------------+------------+-----+ | customer_id | product_id | qty | +-------------+------------+-----+ | 1 | 6 | 3 | +-------------+------------+-----+
What this code does
- The first statement finds no row with key (1, 6), so it inserts: 1 row affected. The second hits that key, so the
UPDATEpart runs and the quantity becomes 1 + 2 = 3: 2 rows affected. carts.qtyis the stored value andnew.qtyis the value in thisVALUES. The cart still has one line for the product, which is what the shop wants.
Example 2 · IPL season table: several rows, insert or update each
CREATE TABLE season_runs ( player VARCHAR(30) PRIMARY KEY, matches INT NOT NULL, runs INT NOT NULL, best INT NOT NULL);
INSERT INTO season_runs VALUES ('Shubman Gill', 1, 63, 63), ('Virat Kohli', 1, 21, 21);
-- Scores from match 2INSERT INTO season_runs (player, matches, runs, best) VALUES ('Virat Kohli', 1, 77, 77), ('Shubman Gill', 1, 8, 8), ('Rinku Singh', 1, 48, 48) AS newON DUPLICATE KEY UPDATE matches = season_runs.matches + 1, runs = season_runs.runs + new.runs, best = GREATEST(season_runs.best, new.best);
SELECT * FROM season_runs ORDER BY runs DESC;+--------------+---------+------+------+ | player | matches | runs | best | +--------------+---------+------+------+ | Virat Kohli | 2 | 98 | 77 | | Shubman Gill | 2 | 71 | 63 | | Rinku Singh | 1 | 48 | 48 | +--------------+---------+------+------+
What this code does
- Each row of the
VALUESlist is handled on its own: Kohli and Gill already exist, so their totals grow; Rinku Singh is new, so he is inserted as given. GREATEST(a, b)returns the larger value, sobestkeeps the highest score.ORDER BY runs DESCsorts from most runs to fewest.
Example 3 · ShopLite price import: count what happened
-- Supplier file: a new price and stock for the mouse, and a new bookINSERT INTO products (product_id, name, category_id, price, stock) VALUES (8, 'Logitech M331 Mouse', 4, 799.00, 30), (16, 'Wings of Fire', 5, 350.00, 40) AS newON DUPLICATE KEY UPDATE price = new.price, stock = new.stock;
SELECT ROW_COUNT() AS affected;
SELECT product_id, name, price, stock FROM products WHERE product_id IN (8, 16) ORDER BY product_id;+----------+ | affected | +----------+ | 3 | +----------+ +------------+---------------------+--------+-------+ | product_id | name | price | stock | +------------+---------------------+--------+-------+ | 8 | Logitech M331 Mouse | 799.00 | 30 | | 16 | Wings of Fire | 350.00 | 40 | +------------+---------------------+--------+-------+
What this code does
- Product 8 exists, so its price and stock are updated (2 affected rows); product 16 is new, so it is inserted (1).
ROW_COUNT()adds them: 3. - Only
priceandstockare in theUPDATEpart, so the stored name and category of product 8 are never touched by the import. Running the same import again would report 0: nothing changes.
Write AS new after VALUES (...) to name the incoming row (MySQL 8.0.19 and later): ON DUPLICATE KEY UPDATE qty = carts.qty + new.qty. Writing plain qty there fails with ERROR 1052 (23000): Column 'qty' in field list is ambiguous, because both rows have a qty. The affected-row count is 0 when the existing row already had those values. Older code writes VALUES(qty) instead of new.qty; it still runs, but it is deprecated since MySQL 8.0.20 and gives warning 1287.
- An upsert that updates may still use up an
AUTO_INCREMENTnumber, so ids can get gaps. Gaps are harmless. - With several unique keys, a row can clash with a different row than you expect. Upsert on one clear key.
Predict the output
Predict the upsert
Asha's cart already holds 1 boAt Airdopes (customer 1, product 6, qty 1).
Round 1 of 2
INSERT IGNORE and its risks
INSERT IGNORE inserts the rows it can and turns each row's error into a warning, so duplicates are skipped without failing. It hides every error, not only duplicates.
Example 4 skips sign-ups that already exist. Example 5 shows the danger: IGNORE also hides bad data.
Example 4 · INSERT IGNORE: skip sign-ups you already have
CREATE TABLE newsletter ( sub_id INT AUTO_INCREMENT PRIMARY KEY, email VARCHAR(60) NOT NULL UNIQUE, city VARCHAR(30) NOT NULL);INSERT INTO newsletter (email, city) VALUES ('asha.rao@example.com', 'Bengaluru');
INSERT IGNORE INTO newsletter (email, city) VALUES ('asha.rao@example.com', 'Bengaluru'), ('ravi.kumar@example.com', 'Chennai');SHOW WARNINGS;
SELECT * FROM newsletter ORDER BY sub_id;+---------+------+-------------------------------------------------------------------+ | Level | Code | Message | +---------+------+-------------------------------------------------------------------+ | Warning | 1062 | Duplicate entry 'asha.rao@example.com' for key 'newsletter.email' | +---------+------+-------------------------------------------------------------------+ +--------+------------------------+-----------+ | sub_id | email | city | +--------+------------------------+-----------+ | 1 | asha.rao@example.com | Bengaluru | | 2 | ravi.kumar@example.com | Chennai | +--------+------------------------+-----------+
What this code does
- Asha's email is already there, so that row is skipped and recorded as warning 1062; Ravi's row is inserted. Without
IGNORE, the whole statement would fail and Ravi would not be added either. SHOW WARNINGSmust come straight after theINSERT IGNORE: any other statement in between, even aSELECT, clears the list.
Example 5 · The risk: IGNORE also hides bad data
INSERT IGNORE INTO customers (name, email, city, joined_on) VALUES ('Asha Rao', 'asha.rao@example.com', 'Bengaluru', '2025-06-01'), ('Tara Das', 'tara.das@example.com', NULL, '2025-06-01');SHOW WARNINGS;
SELECT customer_id, name, city FROM customers WHERE customer_id > 9 ORDER BY customer_id;+---------+------+------------------------------------------------------------------+ | Level | Code | Message | +---------+------+------------------------------------------------------------------+ | Warning | 1062 | Duplicate entry 'asha.rao@example.com' for key 'customers.email' | | Warning | 1048 | Column 'city' cannot be null | +---------+------+------------------------------------------------------------------+ +-------------+------------+-----------+ | customer_id | name | city | +-------------+------------+-----------+ | 10 | Imran Khan | Hyderabad | | 11 | Tara Das | | +-------------+------------+-----------+
What this code does
- The duplicate Asha is skipped, as hoped. But Tara's
NULLcity, which a normalINSERTwould refuse with error 1048, became a warning, and she was saved with an empty city. - Nothing failed, so an application would report success. Only
SHOW WARNINGSreveals the bad row; this is whyINSERT IGNOREneeds care.
A duplicate email is simply skipped, and the statement still "succeeds".
Skipping duplicates sounds handy for "add these sign-ups, skip ones I already have", but the same switch hides other errors. A NULL for a NOT NULL column is stored as an empty string or 0, a name too long for its column is cut short, and rows that break a foreign key or a CHECK vanish without an error. Use INSERT IGNORE only when duplicates are expected and harmless, and run SHOW WARNINGS; straight after it to see what was skipped or changed.
REPLACE and why it deletes first
REPLACE INTO works like INSERT, but when the new row clashes with an existing key it deletes the old row and inserts the new one. It does not update in place.
Example 6 replaces a user, and the old row with its id is gone: REPLACE deletes first, then inserts.
Example 6 · REPLACE deletes the old row first
CREATE TABLE app_users ( user_id INT AUTO_INCREMENT PRIMARY KEY, email VARCHAR(60) NOT NULL UNIQUE, name VARCHAR(30) NOT NULL, phone CHAR(10) NULL);INSERT INTO app_users (email, name, phone) VALUES ('asha.rao@example.com', 'Asha Rao', '9876500001'), ('ravi.kumar@example.com', 'Ravi Kumar', '9876500002');
-- Asha changes her display nameREPLACE INTO app_users (email, name) VALUES ('asha.rao@example.com', 'Asha R.');SELECT ROW_COUNT() AS affected;
SELECT * FROM app_users ORDER BY user_id;+----------+ | affected | +----------+ | 2 | +----------+ +---------+------------------------+------------+------------+ | user_id | email | name | phone | +---------+------------------------+------------+------------+ | 2 | ravi.kumar@example.com | Ravi Kumar | 9876500002 | | 3 | asha.rao@example.com | Asha R. | NULL | +---------+------------------------+------------+------------+
What this code does
- The email clashed, so MySQL deleted Asha's row and inserted a new one: 2 rows affected. Her phone is gone, because the new row did not give one, and her id changed from 1 to 3.
- Anything that stored user 1, such as her orders, would now point to nothing. On a ShopLite parent table, MySQL would refuse the delete with error 1451. The safe tool here is
INSERT ... AS new ON DUPLICATE KEY UPDATE name = new.name.
The delete has side effects. Columns you did not give are not kept; they get their defaults. When the clash is on a UNIQUE column such as email, the new row gets a new AUTO_INCREMENT id, so anything that pointed to the old id is broken. A parent row with children is refused with error 1451, or, with ON DELETE CASCADE, its children are deleted. The affected-row count is 2: one delete plus one insert.
- Insert new rows, change existing ones:
INSERT ... ON DUPLICATE KEY UPDATE. - Add new rows, skip existing ones, and check the warnings:
INSERT IGNORE. - Throw away the old row completely and start again:
REPLACE, only on tables nothing points to.
Next step
Check your understanding
12 quick questions