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!