I would like to set
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.
_id can be NULL (and neither is defined
NOT NULL in the question) you have to consider NULL values. You can use
IS NOT DISTINCT FROM:
UPDATE students SET status = (_id IS NOT DISTINCT FROM 1) WHERE status IS DISTINCT FROM (_id IS NOT DISTINCT FROM 1);
Equivalent in this case:
UPDATE students SET status = CASE WHEN _id = 1 THEN true ELSE false END WHERE status IS DISTINCT FROM (CASE WHEN _id = 1 THEN true ELSE false END);
That's assuming a
UNIQUE constraint on
_id. (As Lennart pointed out in a comment.)