SQL · Theory

Insert On Duplicate Key Update

Theory

37/439

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.

Diagram

  students.id  ← PK (unique)
       ▲
       │ FK
  marks.student_id

Exam tip

INSERT … ON DUPLICATE KEY UPDATE marks = 90;

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.

Short notes

DefINSERT … ON DUPLICATE KEY UPDATE = upsert.
RuleInsert if new, update if PK/UNIQUE hits.
TrapMultiple UNIQUE keys — which one fired?

SQL · Theory

Insert On Duplicate Key Update

← All stacks

Theory

37/439

Insert On Duplicate Key Update

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.

Diagram

  students.id  ← PK (unique)
       ▲
       │ FK
  marks.student_id

Exam tip

INSERT … ON DUPLICATE KEY UPDATE marks = 90;

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.

Short notes

DefINSERT … ON DUPLICATE KEY UPDATE = upsert.
RuleInsert if new, update if PK/UNIQUE hits.
TrapMultiple UNIQUE keys — which one fired?

Insert On Duplicate Key Update

Insert On Duplicate Key Update — sample query

Short notes

Questions

Insert On Duplicate Key Update

Insert On Duplicate Key Update — sample query

Short notes

Questions