3

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;
8

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.

  • thank you, this is was exactly i was looking for but i failed to explain, i will improve myself to ask questions in a proper way!! thanks again;) – Schüler Apr 13 '18 at 5:35
  • The 1st query works for NULL values, too (though possibly also updating rows that don't need it). The 2nd and 3rd variants would fail. (_ID = 1) is not fully equivalent to the CASE expression because it returns NULL for NULL input while the CASE expression (correctly) returns false. And <> also fails with NULL values. – Erwin Brandstetter Apr 14 '18 at 12:21
  • True, updated answer – Lennart Apr 14 '18 at 12:32
  • status <> COALESCE(_ID = 1,false) still fails with NULL in status. – Erwin Brandstetter Apr 14 '18 at 12:52
  • Now, COALESCE(status,false) <> COALESCE(_ID = 1,false) would not update rows with status IS NULL that should be false ... – Erwin Brandstetter Apr 14 '18 at 14:19
2

While status and _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.)

Related:

  • All rows with _id = 1 will be true contrary to I would like to set status to false for all rows except one ;-) – Lennart Apr 14 '18 at 13:55
  • Also true. I was silently assuming a UNIQUE constraint on _id (like the OP seems to do in his pseudo-code). But just silently assuming is a loaded footgun in SQL. – Erwin Brandstetter Apr 14 '18 at 14:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.