REPLACE INTO behavior in MySQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

What is the output after REPLACE INTO with existing primary key?

Consider a table users with columns id (primary key) and name. Initially, it has one row: (1, 'Alice').

What will be the content of the table after executing this query?

REPLACE INTO users (id, name) VALUES (1, 'Bob');

MySQL

CREATE TABLE users (id INT PRIMARY KEY, name VARCHAR(50));
INSERT INTO users VALUES (1, 'Alice');
REPLACE INTO users (id, name) VALUES (1, 'Bob');
SELECT * FROM users ORDER BY id;

A (1, 'Bob')

B (1, 'Alice')

C Empty table

D Error: Duplicate entry for primary key

💡 Hint

REPLACE INTO deletes the existing row with the same primary key and inserts the new one.

REPLACE INTO first deletes the row with id=1 and then inserts the new row (1, 'Bob'). So the table ends up with the new row.

🧠 Conceptual

intermediate

How does REPLACE INTO handle unique key conflicts?

Given a table products with columns id (primary key), sku (unique key), and name, what happens when you run:

REPLACE INTO products (id, sku, name) VALUES (2, 'SKU123', 'New Product');

Assuming a row exists with id=3 and sku='SKU123'.

A The existing row with <code>id=3</code> is updated to <code>id=2</code> and <code>name='New Product'</code>.

B The new row is inserted alongside the existing row, causing duplicate <code>sku</code> error.

C The row with <code>id=3</code> is deleted and the new row inserted with <code>id=2</code> and <code>sku='SKU123'</code>.

D The query fails with a syntax error.

💡 Hint

REPLACE INTO deletes rows that conflict on any unique key, not just primary key.

REPLACE INTO deletes any existing row that conflicts on primary or unique keys before inserting the new row. Here, the row with sku='SKU123' is deleted, then the new row inserted.

📝 Syntax

advanced

Which REPLACE INTO syntax is valid for inserting multiple rows?

Which of the following REPLACE INTO statements correctly inserts multiple rows into a table orders with columns order_id and amount?

A REPLACE INTO orders (order_id, amount) VALUES (1, 100), (2, 200);

B REPLACE INTO orders VALUES (1, 100), (2, 200);

C REPLACE INTO orders (order_id, amount) SET (1, 100), (2, 200);

D REPLACE INTO orders (order_id, amount) VALUES ((1, 100), (2, 200));

💡 Hint

Multiple rows can be inserted by separating value tuples with commas.

Option A uses the correct syntax for inserting multiple rows with REPLACE INTO. Option A is missing column list but may work if table columns match. Option A uses invalid SET syntax. Option A nests tuples incorrectly.

❓ optimization

advanced

Why might REPLACE INTO be less efficient than INSERT ... ON DUPLICATE KEY UPDATE?

Consider a table with many indexes. Which reason explains why REPLACE INTO can be slower than INSERT ... ON DUPLICATE KEY UPDATE?

A REPLACE INTO does not check for duplicate keys, causing errors.

B REPLACE INTO locks the entire table, blocking all other queries.

C REPLACE INTO cannot update existing rows, so it duplicates data.

D REPLACE INTO deletes the existing row before inserting, causing more index updates.

💡 Hint

Deleting and inserting rows affects indexes more than updating.

REPLACE INTO deletes the conflicting row and inserts a new one, which causes more index maintenance than updating the existing row.

🔧 Debug

expert

What error occurs when REPLACE INTO violates a foreign key constraint?

Given a table child with a foreign key referencing parent(id), what happens if you run:

REPLACE INTO child (id, parent_id) VALUES (1, 999);

Assuming no parent row with id=999 exists.

A No error, the row is replaced successfully

B Error: Cannot add or update a child row: a foreign key constraint fails

C The parent row with <code>id=999</code> is automatically created

D The row is inserted with <code>parent_id=999</code> ignoring the constraint

💡 Hint

Foreign key constraints prevent invalid references.

REPLACE INTO still enforces foreign key constraints. If the referenced parent row does not exist, it raises an error.

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