How to Update Data Conditionally with INSERT ... ON DUPLICATE KEY UPDATE?

Conditional INSERT ... ON DUPLICATE KEY UPDATE

When using INSERT ... ON DUPLICATE KEY UPDATE, it may be necessary to apply updates conditionally, only when a specific condition is met. However, the use of WHERE in the UPDATE clause is not supported.

Workaround with IF()

To overcome this limitation, it is possible to utilize the IF() function to evaluate the condition and determine whether the update should be performed. The following syntax illustrates this workaround:

INSERT INTO table_name (column1, column2)
VALUES (value1, value2)
ON DUPLICATE KEY UPDATE
  column2 = IF(condition, new_value, old_value);

Example:

Consider the following scenario:

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 example, the update to last_event_id will only occur if the value of last_event_created_at in the incoming row is later than the existing value in the database. This ensures that the most recent event is recorded.

The above is the detailed content of How to Update Data Conditionally with INSERT ... ON DUPLICATE KEY UPDATE?. For more information, please follow other related articles on the PHP Chinese website!