Conditional ON DUPLICATE KEY UPDATE

Question

I've done some research but nothing seems to fit my needs. I have a database table that contain some data retrived from a webservice.

A user perform some tasks for each record, and then flag it as "processed". So i have an additional db field (not based on data i get from the WS) named "processed" that is set to 0 by default, and to 1 when the user has done his work.

Each day i check the WS, and if the statuscode change i want to update the row and set back processed to 0 (so user can handle it again).

Let's say this is my db...

+------+------------+-------+------------+
| id   | statuscode | foo   | processed  |
+------+------------+-------+------------+
| 123  | 66         | bar   | 1          |
+------+------------+-------+------------+

• If there's not a row with the same key (id) i want to insert a new record.
• If there's a row with the same key and 'foo' change i want to update any value except for 'processed' field.
• If there's a row with the same key and statuscode change i want to update any value and set processed to 0.

I think that ON DUPLICATE KEY UPDATE with some condition could make it work, maybe with some CASE or IF condition... am i wrong? Any suggestion is wellcome, thanks in advance!

Answer 1

Something like this (warning: NULL values not taken care of):

INSERT INTO tableX
  (id, statuscode, foo, processed)
VALUES 
  (?, ?, ?, DEFAULT)
ON DUPLICATE KEY UPDATE
  processed = CASE WHEN statuscode <> VALUES(ststuscode)
                     THEN 0
                   ELSE processed
              END 
, statuscode = VALUES(statuscode)
, foo = VALUES(foo) ;

Answer 2

Slight modification of another answer here, this should do:

INSERT INTO tableX (id, statuscode, foo, processed) 
            VALUES (@id, @statuscode, @foo, @processed) 
ON DUPLICATE KEY UPDATE 
                       foo = IF (statuscode != VALUES(statuscode) OR foo != VALUES(foo), VALUES(foo), foo), 
                       statuscode = IF (statuscode != VALUES(statuscode) OR foo != VALUES(foo), VALUES(statuscode), statuscode), 
                       processed = IF (statuscode != VALUES(statuscode), 0, processed)