How to Implement Conditional Updates in MySQL\'s INSERT ... ON DUPLICATE KEY UPDATE?

Conditional UPDATE in INSERT ... ON DUPLICATE KEY Update

In MySQL, the INSERT ... ON DUPLICATE KEY UPDATE statement allows you to insert a new row or update an existing row if the row with the same unique key already exists. However, sometimes you may require the update portion to be conditional, only executing if a specific condition is met.

Unfortunately, the WHERE clause is not supported in the ON DUPLICATE KEY UPDATE syntax. To overcome this limitation, you can utilize the IF() function within the UPDATE clause.

The IF() function takes three arguments: a condition, a value to return if the condition is true, and a value to return if the condition is false. This allows you to specify a condition that checks whether the extra condition has changed, and then conditionally set the value to be updated.

For example, consider the following statement:

INSERT INTO daily_events (created_on, last_event_id, last_event_created_at)
VALUES ('2010-01-19', 23, '2010-01-19 10:23:11')
ON DUPLICATE KEY UPDATE
  last_event_id = IF(last_event_created_at < VALUES(last_event_created_at), VALUES(last_event_id), last_event_id);

In this statement, the last_event_id column will only be updated if the last_event_created_at column of the existing row is earlier than the value in the VALUES clause. Otherwise, the last_event_id will remain unchanged.

The above is the detailed content of How to Implement Conditional Updates in MySQL\'s INSERT ... ON DUPLICATE KEY UPDATE?. For more information, please follow other related articles on the PHP Chinese website!