How to Efficiently Update or Insert Records in MySQL Based on Their Existence?

MySQL: Updating or Inserting Records Based on Existence

When dealing with a database, it's common to encounter scenarios where you need to either update or insert records based on their existence. If you're working with PHP and MySQL, this question arises often.

In this context, the original query attempts to merge update and insert statements using the IF EXISTS syntax. However, this is not the most efficient approach.

To achieve the desired functionality, you should consider using the INSERT ... ON DUPLICATE KEY UPDATE syntax. Here's the correct syntax:

INSERT INTO <table> (field1, field2, field3, ...) 
VALUES ('value1', 'value2','value3', ...)
ON DUPLICATE KEY UPDATE
field1='value1', field2='value2', field3='value3', ...

With this syntax, you can perform both INSERT and UPDATE operations in a single query. If the record doesn't exist (based on the primary key or unique index), it will be inserted. If the record already exists, it will be updated with the provided values.

It's important to note that the affected rows value for each row will be:

• 1 if the row is inserted as a new row.
• 2 if an existing row is updated.
• 0 if an existing row is set to its current values.

This approach is more efficient and easier to maintain than handling the update and insert operations separately.

The above is the detailed content of How to Efficiently Update or Insert Records in MySQL Based on Their Existence?. For more information, please follow other related articles on the PHP Chinese website!