ON DUPLICATE KEY UPDATE in MySQL - Step-by-Step Execution

Concept Flow - ON DUPLICATE KEY UPDATE

Attempt INSERT row

↓

Check for duplicate key

↓

Insert row

↓

Finish

When inserting a row, MySQL checks if a duplicate key exists. If no duplicate, it inserts. If yes, it updates the existing row instead.

Execution Sample

MySQL

INSERT INTO users (id, name, age)
VALUES (1, 'Alice', 30)
ON DUPLICATE KEY UPDATE age = age + 1;

Try to insert a user with id=1; if id=1 exists, increase age by 1 instead.

Execution Table

Step	Action	Duplicate Key Check	Result	Table State
1	Insert (id=1, name='Alice', age=30)	No duplicate	Row inserted	[{id:1, name:'Alice', age:30}]
2	Insert (id=1, name='Alice', age=30)	Duplicate on id=1	Update age = age + 1	[{id:1, name:'Alice', age:31}]
3	Insert (id=2, name='Bob', age=25)	No duplicate	Row inserted	[{id:1, name:'Alice', age:31}, {id:2, name:'Bob', age:25}]
4	Insert (id=2, name='Bob', age=25)	Duplicate on id=2	Update age = age + 1	[{id:1, name:'Alice', age:31}, {id:2, name:'Bob', age:26}]
5	Insert (id=3, name='Carol', age=22)	No duplicate	Row inserted	[{id:1, name:'Alice', age:31}, {id:2, name:'Bob', age:26}, {id:3, name:'Carol', age:22}]
6	Insert (id=3, name='Carol', age=22)	Duplicate on id=3	Update age = age + 1	[{id:1, name:'Alice', age:31}, {id:2, name:'Bob', age:26}, {id:3, name:'Carol', age:23}]
7	Insert (id=4, name='Dave', age=40)	No duplicate	Row inserted	[{id:1, name:'Alice', age:31}, {id:2, name:'Bob', age:26}, {id:3, name:'Carol', age:23}, {id:4, name:'Dave', age:40}]
8	Insert (id=4, name='Dave', age=40)	Duplicate on id=4	Update age = age + 1	[{id:1, name:'Alice', age:31}, {id:2, name:'Bob', age:26}, {id:3, name:'Carol', age:23}, {id:4, name:'Dave', age:41}]
9	Insert (id=5, name='Eve', age=28)	No duplicate	Row inserted	[{id:1, name:'Alice', age:31}, {id:2, name:'Bob', age:26}, {id:3, name:'Carol', age:23}, {id:4, name:'Dave', age:41}, {id:5, name:'Eve', age:28}]
10	Insert (id=5, name='Eve', age=28)	Duplicate on id=5	Update age = age + 1	[{id:1, name:'Alice', age:31}, {id:2, name:'Bob', age:26}, {id:3, name:'Carol', age:23}, {id:4, name:'Dave', age:41}, {id:5, name:'Eve', age:29}]
11	Attempt Insert (id=6, name='Frank', age=35)	No duplicate	Row inserted	[{id:1, name:'Alice', age:31}, {id:2, name:'Bob', age:26}, {id:3, name:'Carol', age:23}, {id:4, name:'Dave', age:41}, {id:5, name:'Eve', age:29}, {id:6, name:'Frank', age:35}]
12	Attempt Insert (id=6, name='Frank', age=35)	Duplicate on id=6	Update age = age + 1	[{id:1, name:'Alice', age:31}, {id:2, name:'Bob', age:26}, {id:3, name:'Carol', age:23}, {id:4, name:'Dave', age:41}, {id:5, name:'Eve', age:29}, {id:6, name:'Frank', age:36}]
13	Stop	N/A	All inserts processed	Final table state as above

💡 All insert attempts processed; no more rows to insert.

Variable Tracker

Variable	Start	After 1	After 2	After 3	After 4	After 5	After 6	After 7	After 8	After 9	After 10	After 11	After 12	Final
Table rows count	0	1	1	2	2	3	3	4	4	5	5	6	6	6
Alice age	N/A	30	31	31	31	31	31	31	31	31	31	31	31	31
Bob age	N/A	N/A	N/A	25	26	26	26	26	26	26	26	26	26	26
Carol age	N/A	N/A	N/A	N/A	N/A	22	23	23	23	23	23	23	23	23
Dave age	N/A	N/A	N/A	N/A	N/A	N/A	N/A	40	41	41	41	41	41	41
Eve age	N/A	N/A	N/A	N/A	N/A	N/A	N/A	N/A	N/A	28	29	29	29	29
Frank age	N/A	N/A	N/A	N/A	N/A	N/A	N/A	N/A	N/A	N/A	N/A	35	36	36

Key Moments - 3 Insights

Why does the age increase by 1 instead of replacing the old age?

Because the ON DUPLICATE KEY UPDATE clause uses 'age = age + 1', it adds 1 to the existing age instead of overwriting it. See execution_table rows 2, 4, 6, etc.

What happens if there is no duplicate key found?

The row is simply inserted as new without any update. This is shown in execution_table rows 1, 3, 5, etc.

Does ON DUPLICATE KEY UPDATE run if the duplicate key is on a different column?

No, it only triggers if the duplicate key conflicts with a unique or primary key constraint. Here, the duplicate is on 'id'.

Visual Quiz - 3 Questions

Look at the execution_table at step 4. What is Bob's age after the update?

A 25

B 26

C 24

D 27

💡 Hint

Check the 'Table State' column at step 4 in execution_table.

At which step does Carol get inserted into the table?

A Step 6

B Step 3

C Step 5

D Step 2

💡 Hint

Look for the first 'No duplicate' insert action for Carol in execution_table.

If the ON DUPLICATE KEY UPDATE clause was removed, what would happen at step 2?

A Error due to duplicate key

B Age increased by 1

C Row inserted normally

D Row updated with new values

💡 Hint

Without ON DUPLICATE KEY UPDATE, duplicate key insert causes error.

Concept Snapshot

INSERT INTO table (columns) VALUES (values)
ON DUPLICATE KEY UPDATE column = value;

- Tries to insert a row.
- If duplicate key exists, runs UPDATE instead.
- Useful to avoid errors and update existing rows.
- Can use expressions like column = column + 1.

Full Transcript

The ON DUPLICATE KEY UPDATE clause in MySQL lets you try inserting a row. If the row's key already exists, instead of erroring, it updates the existing row. For example, inserting a user with id=1 will add a new row if id=1 is new. If id=1 exists, it updates the age by adding 1. This way, you can keep data fresh without manual checks. The execution table shows each insert attempt, whether it inserts or updates, and the table state after. Variables like age change only on updates. This helps beginners see how MySQL handles duplicates smoothly.

Practice

1. What does the ON DUPLICATE KEY UPDATE clause do in a MySQL INSERT statement?

easy

A. It deletes rows with duplicate keys before inserting new ones.

B. It updates existing rows if a duplicate key is found during insert.

C. It ignores the insert if a duplicate key exists.

D. It creates a new table when a duplicate key is found.

Solution

1. Step 1: Understand the purpose of ON DUPLICATE KEY UPDATE

   This clause allows an INSERT statement to update existing rows if a duplicate key (like a primary key) is found instead of causing an error.

2. Step 2: Compare options with this behavior

   Only It updates existing rows if a duplicate key is found during insert. correctly describes this behavior. The other options describe different actions that are not what this clause does.

3. Final Answer:

   It updates existing rows if a duplicate key is found during insert. -> Option B

4. Quick Check:

   ON DUPLICATE KEY UPDATE = update on duplicate [OK]

Hint: It updates rows when duplicate keys appear during insert [OK]

Common Mistakes:

• Thinking it deletes rows instead of updating
• Assuming it ignores duplicates silently
• Confusing it with creating new tables

2. Which of the following is the correct syntax to use ON DUPLICATE KEY UPDATE in MySQL?

easy

A. INSERT INTO table (id, val) VALUES (1, 'a') ON DUPLICATE KEY UPDATE val = 'b';

B. INSERT INTO table (id, val) VALUES (1, 'a') DUPLICATE KEY UPDATE val = 'b';

C. INSERT INTO table (id, val) VALUES (1, 'a') ON DUPLICATE UPDATE val = 'b';

D. INSERT INTO table (id, val) VALUES (1, 'a') ON DUPLICATE KEY SET val = 'b';

Solution

1. Step 1: Recall the correct syntax format

   The correct syntax is INSERT INTO ... VALUES (...) ON DUPLICATE KEY UPDATE column = value;

2. Step 2: Match options with correct syntax

   Only INSERT INTO table (id, val) VALUES (1, 'a') ON DUPLICATE KEY UPDATE val = 'b'; matches the exact syntax. The other options have incorrect keywords or missing parts.

3. Final Answer:

   INSERT INTO table (id, val) VALUES (1, 'a') ON DUPLICATE KEY UPDATE val = 'b'; -> Option A

4. Quick Check:

   Correct syntax includes 'ON DUPLICATE KEY UPDATE' [OK]

Hint: Remember full phrase: ON DUPLICATE KEY UPDATE [OK]

Common Mistakes:

• Omitting the word KEY
• Using UPDATE without ON DUPLICATE KEY
• Replacing UPDATE with SET

3. Given the table users(id INT PRIMARY KEY, name VARCHAR(20), points INT) with data:
(1, 'Alice', 10)
What will be the result after running?

INSERT INTO users (id, name, points) VALUES (1, 'Alice', 20) ON DUPLICATE KEY UPDATE points = points + 5;

medium

A. A new row with id=1 and points=20 is inserted.

B. The existing row with id=1 is updated to points=5.

C. The existing row with id=1 is updated to points=15.

D. The query causes a duplicate key error.

Solution

1. Step 1: Identify duplicate key and update action

   The id 1 already exists, so the ON DUPLICATE KEY UPDATE clause triggers. It updates points by adding 5 to the existing value.

2. Step 2: Calculate updated points value

   Existing points = 10, so new points = 10 + 5 = 15.

3. Final Answer:

   The existing row with id=1 is updated to points=15. -> Option C

4. Quick Check:

   points = 10 + 5 = 15 [OK]

Hint: Update adds to existing value on duplicate key [OK]

Common Mistakes:

• Thinking it inserts a new row instead of updating
• Assuming points become 20 instead of adding 5
• Expecting an error on duplicate key

4. What is wrong with this query?

INSERT INTO products (id, stock) VALUES (5, 10) ON DUPLICATE KEY UPDATE stock = stock + 1,;

medium

A. The VALUES keyword is not allowed with ON DUPLICATE KEY UPDATE.

B. The ON DUPLICATE KEY UPDATE clause is missing a column.

C. The stock column cannot be updated this way.

D. The comma before the semicolon causes a syntax error.

Solution

1. Step 1: Check syntax of ON DUPLICATE KEY UPDATE clause

   The clause ends with a comma before the semicolon, which is invalid syntax in SQL.

2. Step 2: Identify correct syntax rules

   There should be no trailing comma after the last update assignment.

3. Final Answer:

   The comma before the semicolon causes a syntax error. -> Option D

4. Quick Check:

   Trailing comma in UPDATE clause = syntax error [OK]

Hint: No trailing comma after last update assignment [OK]

Common Mistakes:

• Leaving a comma after last column update
• Confusing VALUES usage with ON DUPLICATE KEY UPDATE
• Thinking stock column can't be updated

5. You want to insert a new user or update their score only if the new score is higher than the existing one. Which query correctly uses ON DUPLICATE KEY UPDATE for this?

hard

A. INSERT INTO users (id, score) VALUES (2, 50) ON DUPLICATE KEY UPDATE score = IF(score < VALUES(score), VALUES(score), score);

B. INSERT INTO users (id, score) VALUES (2, 50) ON DUPLICATE KEY UPDATE score = VALUES(score);

C. INSERT INTO users (id, score) VALUES (2, 50) ON DUPLICATE KEY UPDATE score = score + VALUES(score);

D. INSERT INTO users (id, score) VALUES (2, 50) ON DUPLICATE KEY UPDATE score = score - VALUES(score);

Solution

1. Step 1: Understand the requirement

   We want to update the score only if the new score is greater than the existing score.

2. Step 2: Analyze each option's update logic

   INSERT INTO users (id, score) VALUES (2, 50) ON DUPLICATE KEY UPDATE score = VALUES(score); always replaces the score, ignoring if it's higher or not. INSERT INTO users (id, score) VALUES (2, 50) ON DUPLICATE KEY UPDATE score = IF(score < VALUES(score), VALUES(score), score); uses IF to update only if new score is higher. INSERT INTO users (id, score) VALUES (2, 50) ON DUPLICATE KEY UPDATE score = score + VALUES(score); adds scores, which is not the requirement. INSERT INTO users (id, score) VALUES (2, 50) ON DUPLICATE KEY UPDATE score = score - VALUES(score); subtracts scores, which is incorrect.

3. Final Answer:

   INSERT INTO users (id, score) VALUES (2, 50) ON DUPLICATE KEY UPDATE score = IF(score < VALUES(score), VALUES(score), score); -> Option A

4. Quick Check:

   Use IF to update only if new score is higher [OK]

Hint: Use IF(condition, new, old) to update conditionally [OK]

Common Mistakes:

• Always overwriting score without condition
• Adding scores instead of comparing
• Subtracting scores mistakenly