Combining 2 Mysql update statments(same col, different values, different conditions)

I have been doing some searching but have not been able to find an answer for this so thought I would ask here as th开发者_如何学Ce people here know everything :)

I am trying to combine these 2 update queries into one query.

UPDATE addresses SET is_default='0' WHERE id!='1'
UPDATE addresses SET is_default='1' WHERE id='1'

I assume this should be too hard to accomplish but i cant seem to work it out :(

Thanks

Paul

You can use CASE to do this:

UPDATE addresses SET is_default = CASE WHEN id = 1 THEN 1 ELSE 0 END;

In your example, you're updating the entire table, so you wouldn't benefit from indexes no matter what, but if you were only doing a subset of values, you'd still want to leave the where in (because its very doubtful the optimizer could figure out how to use the index). For example:

UPDATE foo
  SET
    bar = CASE id WHEN 1 THEN 1 WHEN 2 THEN 0 ELSE bar END
    baz = CASE WHEN id = 3 THEN 7 ELSE baz END
  WHERE id IN (1,2,3)

(Note the two different CASE syntaxes).

UPDATE addresses SET is_default=IF(id='1', '1', '0')

Try this:

UPDATE addresses SET is_default= IF(id!='1', '0', '1')