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.