CODEWITHSUNDEEP
mysqlconditional-statementson-duplicate-key
Luciano
Luciano

Reputation: 1455

Conditional ON DUPLICATE KEY UPDATE

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          |
+------+------------+-------+------------+

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!

Upvotes: 6

Views: 2309

Answers (2)

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115520

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

Upvotes: 7

nawfal
nawfal

Reputation: 73163

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)

Upvotes: 1

Related Questions