Insert On Duplicate Key Update — SQL Technical Interview Questions & Answers

Insert On Duplicate Key Update

Insert On Duplicate Key Update

  • INSERT INTO students (roll, name, marks) VALUES (12, 'Asha', 90) ON DUPLICATE KEY UPDATE marks = VALUES(marks); If roll 12 is new, insert. If PK/UNIQUE clashes, update marks. This is the usual upsert.
  • MySQL 8.0.20+ prefers aliases: AS new THEN marks = new.marks. VALUES(col) in the UPDATE clause is older style.
  • Trap: ON DUPLICATE KEY without understanding which UNIQUE key clashed — you might update the wrong kind of conflict (email vs roll).
  • On the example next to this theory: Insert On Duplicate Key Update — iNSERT adds rows. List columns explicitly for clarity.

Technical Interview Tip: INSERT … ON DUPLICATE KEY UPDATE marks = 90;

Code Examples for Insert On Duplicate Key Update

Insert On Duplicate Key Update — sample query

CREATE TABLE products (id INTEGER PRIMARY KEY, name TEXT, price REAL);
INSERT INTO products (id, name, price) VALUES
  (1, 'Mouse', 499),
  (2, 'Keyboard', 999);
SELECT * FROM products;

Insert On Duplicate Key Update — iNSERT adds rows. List columns explicitly for clarity.

How to Explain Insert On Duplicate Key Update to an Interviewer

What is an upsert in MySQL?

INSERT … ON DUPLICATE KEY UPDATE Insert or update on key clash