MySQL Update field of set IF NULL or other value

In your case you could use CASE*:

UPDATE Table1
SET field1 = field1 + 1,
  field2 = NOW(),
  field3 =
        CASE
        WHEN field3 < '2011-00-00 00:00:00' THEN /* Evaluates to false if NULL */
          NOW()
        WHEN field3 IS NULL THEN
          NOW()
        ELSE /* Don't change */
          field3
        END
WHERE id = 1

*Pun optional


You can do it like that:

UPDATE `Table1` 
SET `field1` = `field1` + 1,
    `field2` = NOW(),
    `field3` = COALESCE(field3, NOW())
WHERE id = 1;

This will update all field1 and field2 and if field3 is null will update it too, if its not null it will remain what it was. But this works only for NULL!!!


I think that's it's possible for you to do this using an IF statement. The IF statement takes 3 parameters when you're using it: the expression, value if true, value if false

So in your case, you could probably write your queries in one go like the following:

UPDATE Table1 
SET 
    field1 = field1 + 1, 
    field2 = NOW(),
    field3 = IF(field3 < '2011-00-00 00:00:00' OR field3 IS NULL, NOW(), field3)
WHERE id = $id;

This way, if expression is true, then field3 will be NOW() and otherwise, it'll remain as it was.

Tags:

Mysql