Custom Search
 


Using MySQL REPLACE (INTO) to mimic DELETE + INSERT


MySQL REPLACE (INTO) function is a combined operation of DELETE + INSERT. It works like this: when an existing row in the table has the same value as the new row after matching on a primary key or a unique index, the existing row is deleted, then the new row is inserted. If the new row does not exist in the table in terms of a matching primary key or unique index, the new row is inserted.

In this tutorial, we'll use a table called email_sent_count to demonstrate how REPLACE works. The table tracks how many times an email address has been contacted.

When using MySQL REPLACE function, consider the following guidelines.

  1. The table must have a UNIQUE key or PRIMARY key constraint defined which MySQL can use to check if duplicate value exists. See Practice #1.

  2. If the table does not have a primary key or unique key index, REPLACE becomes equivalent to INSERT, because there is no key/index to be checked to determine whether the new row has a duplicate counterpart. See Practice #2.

  3. INSERT IGNORE is different to REPLACE. If unique key/row exists, INSERT IGNORE skips insertion so it effectively ignores the row in the insert statement.

Using REPLACE does have unexpected side effects or limitations.

  1. REPLACE wastes AUTO_INCREMENT primary key IDs as a new auto-increment ID is allocated when it inserts a matched row.

  2. If a primary key table has referential integrity (foreign key) enforced, the REPLACE operation would fail because the MySQL prevents a parent row from being deleted when one or more child rows exist in other table(s).

  3. If the primary key table has cascading foreign keys (a foreign key constraint ON DELETE CASCADE), rows in the foreign key tables would be deleted automatically.

  4. If you need to maintain history of changes for the records, don't use REPLACE because the matched row is deleted.

  5. If you have DELETE triggers in the table, they would be fired automatically and may have unexpected effect on the table.

  6. REPLACE INTO is a very slower operation because it involves both delete and insert.

Based on the side effects listed above, you should consider using INSERT...ON DUPLICATE KEY UPDATE instead of REPLACE.

Practice #1: DELETE first, then INSERT.

The email column is unique. ID column is auto increment which is also unique, but checking if a record exists is based on email column rather than the ID column.

Email address tony@abcdmnxyz.com already exists in the table, so it will trigger a delete (ID 2 will be deleted), then ID 5 will be inserted.

REPLACE INTO email_sent_count(Email, SentCount, SentTime, HashValue)
VALUES ('tony@abcdmnxyz.com', 10, '2016-04-11 11:40:21', 'c9defc67f7f3ef0894e85ec9ebc92f0a')

Before the REPLACE query, the table looks as below.

After the REPLACE query, the table looks as below.

Practice #2: No unique index exists, REPLACE becomes equivalent to INSERT.

First, we remove the unique index on the email column.

DROP INDEX Uidx_Email ON email_sent_count

Then run the REPLACE query, the table looks as below. Note that there are two records for tony@abcdmnxyz.com because it was not replaced as we have removed the unique index.

REPLACE INTO email_sent_count(Email, SentCount, SentTime, HashValue)
VALUES ('tony@abcdmnxyz.com', 10, '2016-04-11 11:40:21', 'c9defc67f7f3ef0894e85ec9ebc92f0a')

Happy coding!



Other tutorials in this category

1. Update Statement in MySQL

2. How to update data in a specific order in MySQL

3. How to update top N rows of data in MySQL

4. Use outer join in update

5. How to do cross-table update in MySQL - Part 1

6. How to do cross-table update in MySQL - Part 2

7. Using Bulk Insert Statement

8. Basic insert statement

9. How to create and use AUTO_INCREMENT column and use LAST_INSERT_ID function

10. Combine update and insert into one statement

11. Update multiple rows (distinctive by primary key) with different values in one query

12. Update multiple rows (distinctive by primary key) for multiple columns in one query

Back to Tutorial Index Page


Copyright © 2024 GeeksEngine.com. All Rights Reserved.

This website is hosted by HostGator.

No portion may be reproduced without my written permission. Software and hardware names mentioned on this site are registered trademarks of their respective companies. Should any right be infringed, it is totally unintentional. Drop me an email and I will promptly and gladly rectify it.

 
Home | Feedback | Terms of Use | Privacy Policy