|
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.
Using REPLACE does have unexpected side effects or limitations.
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) 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)
Happy coding! Other tutorials in this category 1. Update Statement in MySQL |
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 |