Update query with two different where conditions

I would like to set status to false for all rows except one, which should should be set to true. How can I achieve that?

Here is the query I tried:

UPDATE students set status = false where status = true and 
set status = true where _id = 1;

You can use a case expression:

UPDATE students 
    set status = CASE WHEN _ID = 1 THEN true
                                   ELSE false
                 END;

A bit shorter (but perhaps not as obvious) is:

UPDATE students 
    set status = COALESCE(_ID = 1,false);

COALESCE is need if _ID can be NULL.

One may argue that it is not necessary to update status at all times (for efficiency reasons). If we take into consideration that both _ID and status can be null there are 9 different situations (we let _ID = 0 represent all rows where _ID is not null and _ID <> 1).

_ID    Status    Action
-----------------------
null   null      set false
null   true      set false
null   false     -
   0   null      set false
   0   true      set false
   0   false     -
   1   null      set true
   1   true      - 
   1   false     set true

All Actions is handled by:

set status = COALESCE(_ID = 1,false);

So we need to filter out the 3 situations where no update should take place:

(null, false), (0, false), (1, true)

In other words, our WHERE clause should include the other 6 combinations:

UPDATE students 
    set status = COALESCE(_id = 1,false)
WHERE ((coalesce(_id, 0) = 1) <> coalesce(status,(coalesce(_id, 0) <> 1)))

This is not trivial (IMO), and even though it probably can be simplified I would not recommend to use it in any real situation (I would recommend disallowing nulls for boolean attributes in the first place, but that's another story).

A useful technique (once again IMO) to verify boolean expressions with truth tableau's, is to use CTE's for the domains and investigate the result. Example from above:

with status (s) as ( values (null),(false),(true))
  ,     _id (i) as ( values (null),(0),(1))
select i, s from status cross join _id
where ((coalesce(i, 0) = 1) <> coalesce(s,(coalesce(i, 0) <> 1)));

IS DISTINCT FROM as demonstrated by @Erwin Brandstetter is a nice abstraction that is very useful in situations like these.

StackExchange Database Administrators Q#203830, answer score: 8