Recently, for a project I am working on, I needed a conditional
update of one column's value with the value of a different column
part of the conditional. Luckily, this is possible and the syntax
is what I expected it to be. In my specific case I wanted to
keep track of the total times an object was updated, and the number
of times it has been updated on the current list. Hence I needed
two counters: one for the total number of updates, and one that's
reset to 0 each time the list_id changes. Both columns
where set default to zero. An outline of the SQL statement I use
follows:
INSERT ...
ON DUPLICATE KEY UPDATE
times_updated = times_updated + 1,
times_updated_on_current_list =
IF(list_id = VALUES(list_id), times_updated_on_current_list + 1, 0),
list_id = VALUES(list_id)
The above code, on a duplicate-key conflict, will increase the
value of times_updated by one, always, keeping
track of the total number of updates. If the current value of
list_id is the same as the new one the value of
times_updated_on_current_list will be incremented
by one. Otherwise, its value will be reset to 0.
Remember that VALUES(list_id) returns the value
that would be inserted, had no duplicate-key conflict occured.
Since we assign this value to the list_id column
it's important that this update happens after the
conditional that checks if it is going to be changed. If the
order is changed, the conditional will always be true and hence
times_updated_on_current_list will always have
the same value as times_updated