ON DUPLICATE KEY UPDATE in MySQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Effect of ON DUPLICATE KEY UPDATE on existing row

Given the table users with columns id (primary key) and score, what will be the content of the table after executing the following query?

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

Assume the table initially contains: {id: 1, score: 20}.

A {id: 1, score: 10}

B {id: 1, score: 25}

C {id: 1, score: 15}

D {id: 1, score: 20}

💡 Hint

Think about what happens when the primary key already exists.

The ON DUPLICATE KEY UPDATE clause updates the existing row by adding 5 to the current score (20 + 5 = 25).

🧠 Conceptual

intermediate

Behavior when no duplicate key exists

What happens when you run this query on a table where the primary key does not exist yet?

INSERT INTO products (product_id, quantity) VALUES (5, 100) ON DUPLICATE KEY UPDATE quantity = quantity + 10;

A The query fails with a duplicate key error.

B The existing row with product_id 5 is updated to quantity 110.

C A new row with product_id 5 and quantity 100 is inserted.

D No changes happen to the table.

💡 Hint

Consider what happens if the key is not found.

If the key does not exist, the row is inserted normally with the given values.

📝 Syntax

advanced

Correct syntax for ON DUPLICATE KEY UPDATE

Which of the following queries is syntactically correct in MySQL?

INSERT INTO inventory (item_id, stock) VALUES (3, 50) ON DUPLICATE KEY UPDATE stock = stock - 1;

A INSERT INTO inventory (item_id, stock) VALUES (3, 50) ON DUPLICATE UPDATE stock = stock - 1;

B INSERT INTO inventory (item_id, stock) VALUES (3, 50) DUPLICATE KEY UPDATE stock = stock - 1;

C INSERT INTO inventory (item_id, stock) VALUES (3, 50) ON DUPLICATE KEY SET stock = stock - 1;

D INSERT INTO inventory (item_id, stock) VALUES (3, 50) ON DUPLICATE KEY UPDATE stock = stock - 1;

💡 Hint

Check the exact keywords and their order.

The correct syntax uses ON DUPLICATE KEY UPDATE followed by the column assignments.

❓ optimization

advanced

Optimizing multiple row inserts with ON DUPLICATE KEY UPDATE

You want to insert multiple rows into a table and update existing rows if keys duplicate. Which query is the most efficient?

A Use a single multi-row INSERT ... ON DUPLICATE KEY UPDATE query with all rows.

B Run multiple single-row INSERT ... ON DUPLICATE KEY UPDATE queries, one per row.

C Delete all rows first, then insert all rows without ON DUPLICATE KEY UPDATE.

D Use UPDATE queries for existing rows and INSERT for new rows separately.

💡 Hint

Think about reducing the number of queries sent to the database.

A single multi-row INSERT with ON DUPLICATE KEY UPDATE reduces overhead and is more efficient.

🔧 Debug

expert

Diagnosing unexpected behavior with ON DUPLICATE KEY UPDATE

Consider this table orders with a unique key on order_number. You run:

INSERT INTO orders (order_number, status) VALUES ('A123', 'pending') ON DUPLICATE KEY UPDATE status = 'pending';

But the status does not update when you expect it to. What is the most likely cause?

A The existing row already has status 'pending', so no change occurs.

B The query syntax is incorrect and causes a silent failure.

C The database does not support ON DUPLICATE KEY UPDATE.

D The unique key is not on order_number, so no duplicate is detected.

💡 Hint

Think about what happens if the update sets the same value as before.

If the existing row's status is already 'pending', the update does not change the data, so it may seem like nothing happened.