Perl programmer for hire: download my resume (PDF).
John Bokma MexIT
freelance Perl programmer


Thursday, March 15, 2012 | 0 comments

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:

    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

ON DUPLICATE KEY with IF related

Also today

Please post a comment | read 0 comments | RSS feed