REPLACE INTO behavior in MySQL - Step-by-Step Execution

Concept Flow - REPLACE INTO behavior

Start REPLACE INTO

↓

Check if row with same PRIMARY or UNIQUE key exists

↓

Delete old

↓

End REPLACE INTO

REPLACE INTO first checks if a row with the same key exists; if yes, it deletes it, then inserts the new row.

Execution Sample

MySQL

REPLACE INTO behavior (id, action) VALUES (1, 'run');
REPLACE INTO behavior (id, action) VALUES (2, 'jump');
REPLACE INTO behavior (id, action) VALUES (1, 'walk');

This code inserts two rows, then replaces the row with id=1 with new data.

Execution Table

Step	Query Executed	Check Existing Row	Action Taken	Table State After
1	REPLACE INTO behavior (id, action) VALUES (1, 'run')	No row with id=1	Insert new row (id=1, action='run')	[{id:1, action:'run'}]
2	REPLACE INTO behavior (id, action) VALUES (2, 'jump')	No row with id=2	Insert new row (id=2, action='jump')	[{id:1, action:'run'}, {id:2, action:'jump'}]
3	REPLACE INTO behavior (id, action) VALUES (1, 'walk')	Row with id=1 exists	Delete old row id=1, Insert new row (id=1, action='walk')	[{id:1, action:'walk'}, {id:2, action:'jump'}]

💡 All queries executed; REPLACE INTO ends after inserting or replacing rows.

Variable Tracker

Table 'behavior'	Start	After Step 1	After Step 2	After Step 3
Rows	[]	[{id:1, action:'run'}]	[{id:1, action:'run'}, {id:2, action:'jump'}]	[{id:1, action:'walk'}, {id:2, action:'jump'}]

Key Moments - 2 Insights

Why does REPLACE INTO delete a row before inserting?

Because REPLACE INTO ensures no duplicate PRIMARY or UNIQUE keys exist by deleting the old row first, as shown in step 3 of the execution_table.

What happens if no existing row matches the key?

REPLACE INTO simply inserts the new row without deleting anything, as seen in steps 1 and 2 of the execution_table.

Visual Quiz - 3 Questions

Look at the execution_table, what is the table state after step 2?

A [{id:1, action:'walk'}, {id:2, action:'jump'}]

B [{id:1, action:'run'}, {id:2, action:'jump'}]

C [{id:1, action:'run'}]

D []

💡 Hint

Check the 'Table State After' column for step 2 in the execution_table.

At which step does REPLACE INTO delete an existing row?

A Step 3

B Step 2

C Step 1

D No deletion occurs

💡 Hint

Look at the 'Action Taken' column in the execution_table for the step mentioning deletion.

If the third query used a new id 3 instead of 1, what would happen?

A It would update the existing row with id=1

B It would delete a row and insert new row

C It would insert a new row without deletion

D It would cause an error

💡 Hint

Refer to how REPLACE INTO behaves when no existing row matches the key in steps 1 and 2.

Concept Snapshot

REPLACE INTO syntax:
REPLACE INTO table (columns) VALUES (values);

Behavior:
- Checks for existing row with same PRIMARY/UNIQUE key
- If exists, deletes old row
- Inserts new row

Use to insert or update rows in one command.

Full Transcript

REPLACE INTO in MySQL works by first checking if a row with the same primary or unique key exists. If it does, the old row is deleted. Then, the new row is inserted. If no existing row matches, it simply inserts the new row. This behavior is shown in the example where three REPLACE INTO commands run: the first inserts a row with id=1, the second inserts id=2, and the third replaces the row with id=1 by deleting the old and inserting the new. This ensures no duplicate keys exist. The execution table tracks each step, showing the query, check for existing rows, action taken, and the table state after. Key moments clarify why deletion happens and what occurs if no matching row exists. The visual quiz tests understanding of these steps. The snapshot summarizes syntax and behavior for quick reference.

Practice

1. What does the REPLACE INTO statement do in MySQL?

easy

A. Inserts a new row or replaces an existing row with the same unique key.

B. Only inserts a new row and never updates existing rows.

C. Deletes all rows in the table before inserting new data.

D. Updates all rows in the table without inserting new ones.

Solution

1. Step 1: Understand the purpose of REPLACE INTO

   The REPLACE INTO statement either inserts a new row or replaces an existing row if a unique key conflict occurs.

2. Step 2: Compare with other statements

   Unlike INSERT, it can replace rows, and unlike DELETE, it does not remove all rows before inserting.

3. Final Answer:

   Inserts a new row or replaces an existing row with the same unique key. -> Option A

4. Quick Check:

   REPLACE INTO = Insert or replace [OK]

Hint: REPLACE INTO inserts or updates by unique key conflict [OK]

Common Mistakes:

• Thinking REPLACE INTO only inserts without replacing
• Confusing REPLACE INTO with DELETE
• Assuming REPLACE INTO updates without deleting first

2. Which of the following is the correct syntax for using REPLACE INTO in MySQL?

easy

A. REPLACE INTO table_name (col1, col2) VALUES (val1, val2);

B. REPLACE table_name SET col1=val1, col2=val2;

C. REPLACE INTO table_name VALUES col1=val1, col2=val2;

D. REPLACE INTO table_name (col1, col2) SET (val1, val2);

Solution

1. Step 1: Recall REPLACE INTO syntax

   The correct syntax uses REPLACE INTO table_name (columns) VALUES (values); format.

2. Step 2: Identify incorrect options

   Options A, C, and D use invalid syntax like missing INTO, wrong SET usage, or wrong VALUES format.

3. Final Answer:

   REPLACE INTO table_name (col1, col2) VALUES (val1, val2); -> Option A

4. Quick Check:

   Correct syntax = REPLACE INTO table_name (col1, col2) VALUES (val1, val2); [OK]

Hint: Use REPLACE INTO table (cols) VALUES (vals) syntax [OK]

Common Mistakes:

• Omitting INTO keyword
• Using SET instead of VALUES
• Incorrect parentheses placement

3. Given a table users(id INT PRIMARY KEY, name VARCHAR(20)) with rows:
(1, 'Alice'), (2, 'Bob')
What will be the result after executing:
REPLACE INTO users VALUES (2, 'Charlie');
Then selecting all rows ordered by id?

medium

A. [(1, 'Alice')]

B. [(1, 'Alice'), (2, 'Bob'), (2, 'Charlie')]

C. [(2, 'Charlie')]

D. [(1, 'Alice'), (2, 'Charlie')]

Solution

1. Step 1: Understand REPLACE INTO effect on existing row

   Since id=2 exists, REPLACE deletes that row and inserts the new one with 'Charlie'.

2. Step 2: Check final table rows

   Row with id=1 remains unchanged, row with id=2 is replaced by (2, 'Charlie').

3. Final Answer:

   [(1, 'Alice'), (2, 'Charlie')] -> Option D

4. Quick Check:

   Row with id=2 replaced = [(1, 'Alice'), (2, 'Charlie')] [OK]

Hint: REPLACE deletes existing row with same key before insert [OK]

Common Mistakes:

• Expecting duplicate keys after REPLACE
• Thinking REPLACE only updates without deleting
• Assuming original row remains unchanged

4. You run this query:
REPLACE INTO products (id, name) VALUES (1, 'Pen');
But get an error: ERROR 1146 (42S02): Table 'products' doesn't exist
What is the likely cause?

medium

A. The id column is not a unique or primary key.

B. You forgot to specify the VALUES keyword.

C. The table products does not exist.

D. You used REPLACE INTO instead of INSERT INTO.

Solution

1. Step 1: Check syntax correctness

   The query syntax is correct with REPLACE INTO products (id, name) VALUES (1, 'Pen');.

2. Step 2: Identify cause of table error

   Error 1146 indicates the table products does not exist or is misspelled.

3. Final Answer:

   The table products does not exist. -> Option C

4. Quick Check:

   Table doesn't exist error (1146) means the table is missing [OK]

Hint: Syntax error often means missing or misspelled table [OK]

Common Mistakes:

• Assuming table error means wrong REPLACE INTO usage
• Ignoring table existence
• Confusing table error with syntax error

5. You have a table inventory(product_id INT PRIMARY KEY, quantity INT) with rows:
(101, 5), (102, 10)
You want to add 3 more units to product 101 or insert it if missing.
Which query correctly uses REPLACE INTO to do this?

hard

A. REPLACE INTO inventory VALUES (101, quantity + 3);

B. REPLACE INTO inventory VALUES (101, 8);

C. REPLACE INTO inventory (product_id, quantity) VALUES (101, (SELECT quantity + 3, 0 FROM inventory WHERE product_id=101));

D. REPLACE INTO inventory (product_id, quantity) VALUES (101, 3) ON DUPLICATE KEY UPDATE quantity = quantity + 3;

Solution

1. Step 1: Understand REPLACE INTO behavior

   REPLACE deletes existing row with product_id=101 and inserts new row with given quantity.

2. Step 2: Check options for correctness

   REPLACE INTO inventory VALUES (101, 8); replaces quantity with 8 (5+3). REPLACE INTO inventory VALUES (101, quantity + 3); is invalid syntax. REPLACE INTO inventory (product_id, quantity) VALUES (101, (SELECT quantity + 3, 0 FROM inventory WHERE product_id=101)); uses subquery in VALUES which is not allowed. REPLACE INTO inventory (product_id, quantity) VALUES (101, 3) ON DUPLICATE KEY UPDATE quantity = quantity + 3; mixes REPLACE and ON DUPLICATE KEY UPDATE which is invalid.

3. Final Answer:

   REPLACE INTO inventory VALUES (101, 8); -> Option B

4. Quick Check:

   REPLACE needs full new row, no expressions [OK]

Hint: REPLACE INTO needs full values, no expressions or subqueries [OK]

Common Mistakes:

• Using expressions like quantity + 3 directly in VALUES
• Mixing REPLACE INTO with ON DUPLICATE KEY UPDATE
• Using subqueries inside VALUES clause