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!

how are u going to detect `foo` change? Do you have a value for urself to check it against foo column?? — nawfal, Aug 08 '12 at 19:35

ypercubeᵀᴹ · Accepted Answer · 2012-08-08T20:36:15.887

7

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) ;

edited Aug 08 '12 at 20:36

answered Aug 08 '12 at 19:47

ypercubeᵀᴹ

113,259
19
174
235

shouldn't there be two more checking for fields foo and statuscode? – nawfal Aug 08 '12 at 20:26
@nawfal: Two more? why? If `foo` changes or not is irrelevant, it's taken care by the `ELSE` part. – ypercubeᵀᴹ Aug 08 '12 at 20:29
only if foo or statuscode change, only then he want other fields to be updatable. See my answer, you would understand what I am talking of – nawfal Aug 08 '12 at 20:31
But there are no other fields inserted/updated, but `foo` and `statuscode`. – ypercubeᵀᴹ Aug 08 '12 at 20:33
Hmm I get it. I was assuming there could be more fields and OP showed just an example. Thanks. – nawfal Aug 08 '12 at 20:40
@newfal: You are right that if there were more fields, the code might had to be different - and more complicated. – ypercubeᵀᴹ Aug 08 '12 at 21:10
Yes, you said it had a default of 0, so yes. Or you can change this code and put `0` there: `(?, ?, ?, 0)` That is used for the insertions, not the updates. – ypercubeᵀᴹ Aug 08 '12 at 21:52

score 1 · Answer 2 · answered Aug 08 '12 at 20:21

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)

Conditional ON DUPLICATE KEY UPDATE

2 Answers2