How to Perform Conditional Updates in INSERT ... ON DUPLICATE KEY UPDATE Statements?

INSERT ... ON DUPLICATE KEY UPDATE: Conditional Updates

When performing an INSERT ... ON DUPLICATE KEY UPDATE operation, it is sometimes necessary to conditionally execute the update based on specific conditions. However, the WHERE clause is not permitted with the UPDATE portion of this statement.

Workaround Using IF()

To achieve conditional updates, one solution is to utilize the IF() function. By evaluating a condition within the IF() expression, you can specify a different value for the updated column based on the outcome.

Example

Consider the following table:

CREATE TABLE daily_events (
  created_on DATE,
  last_event_id INT,
  last_event_created_at DATETIME
);

To conditionally update the last_event_id column only if the last_event_created_at column has changed, you can use the following query:

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 query, the IF() function checks whether the value of last_event_created_at in the database is less than the value being inserted. If true, the last_event_id is updated to the inserted value; otherwise, the existing value is retained.

Replication Compatibility

This approach is compatible with replication, as it does not involve any additional queries or modifications to the database schema.

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