REPLACE INTO behavior in MySQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What does the REPLACE INTO statement do in MySQL?

It inserts a new row into a table. If a row with the same primary key or unique key already exists, it deletes that row first and then inserts the new row.

intermediate

How does REPLACE INTO differ from INSERT INTO ... ON DUPLICATE KEY UPDATE?

REPLACE INTO deletes the old row and inserts a new one, which can cause triggers and auto-increment values to behave differently. INSERT ... ON DUPLICATE KEY UPDATE updates the existing row without deleting it.

intermediate

What happens to auto-increment values when using REPLACE INTO?

Because REPLACE INTO deletes the old row and inserts a new one, the auto-increment counter increases as if a new row was inserted, even if replacing an existing row.

advanced

Can REPLACE INTO cause loss of data in some columns?

Yes. Since it deletes the old row and inserts a new one, any columns not specified in the REPLACE INTO statement will be set to their default values, potentially losing data.

beginner

When should you prefer REPLACE INTO over INSERT INTO?

Use REPLACE INTO when you want to ensure a row is inserted or replaced entirely based on unique keys, and you want to overwrite the entire row rather than update specific columns.

What does REPLACE INTO do if a row with the same primary key exists?

A Deletes the old row and inserts the new row

B Updates the existing row without deleting

C Ignores the new row

D Throws an error

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

Which statement is true about auto-increment values with REPLACE INTO?

A Auto-increment value stays the same

B Auto-increment value decreases

C Auto-increment value increases as if a new row was inserted

D Auto-increment value resets to zero

Because REPLACE INTO deletes and inserts a row, the auto-increment counter increases.

What happens to columns not specified in a REPLACE INTO statement?

A They are ignored

B They keep their old values

C They cause an error

D They are set to default values

Since the old row is deleted, unspecified columns get default values on insert.

Which is a key difference between REPLACE INTO and INSERT ... ON DUPLICATE KEY UPDATE?

A <code>REPLACE INTO</code> deletes and reinserts rows

B <code>REPLACE INTO</code> updates rows without deleting

C <code>INSERT ... ON DUPLICATE KEY UPDATE</code> deletes rows

D Both behave exactly the same

REPLACE INTO deletes the old row and inserts a new one, unlike INSERT ... ON DUPLICATE KEY UPDATE.

When is it best to use REPLACE INTO?

A When you want to update only some columns

B When you want to insert or fully replace a row

C When you want to ignore duplicates

D When you want to delete rows

REPLACE INTO is used to insert or fully replace a row based on unique keys.

Explain how REPLACE INTO works in MySQL and what happens to existing rows with the same key.

Think about what happens if a duplicate key exists.

Describe the differences between REPLACE INTO and INSERT ... ON DUPLICATE KEY UPDATE.

Focus on how rows are handled differently.