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.