There is a table with an active column, only one record can be active at a time. You need to make UPDATE records and set the active value to true, and at the same time set false to the active value in the old record. Tell me how to do it in one request.

Example

Source table

id name active 1 a true 2 b false 3 c false

After

id name active 1 a false 2 b true 3 c false

  • I will clarify, for example, a record with id 1 has the status true, I need to activate record 2 (for example, there can be any other), when activating a record with id 2, id 1 should simultaneously set active to false - sscream
  • Hmm, your example is similar to the truth, but something doesn’t work for me - sscream
  • 2
    In my opinion, the idea itself is vicious. It is ridiculous to change the set if only one value is significant. Keep the active record key aside. - Evgeny Borisov

4 answers 4

Update changing one active record to true and all other records that are currently active to false (ensures that only one record in the table can be true):

 with Q as ( select * from testBool where active=true or id=3 ) update testBool T set active=not T.active from Q where exists(select 1 from Q where active=false) and T.id=Q.id

    There is such a crazy option:

     UPDATE actives SET active = (id = 1); -- ^^^^^^^^ это ж boolean!

    The condition will be met if there is a UNIQUE on the sample field, so that only one row of the table can be true from the equality. However, if there is no such key in the table at all , everything will be reset to false . You can add a condition to an id containing a SELECT from the same table to avoid such situations.

    I consider it insane because the base is unlikely to guess how many records will really change as a result of the request, and we know that there are always no more than two . The base will bypass all lines.

    But it is necessary to measure the performance and find out, perhaps this problem exists only in my head. But even if not, a quicker integrity integrity solution may simply not exist. I dont know.

    Yevgeny Borisov correctly notes in the comments to the question that it is much more efficient to store the primary key of the active record in another place. The only place you can atomically change with much smaller locks.

      With the help of triggers:

       Crteate trigger trig Before inser or update or delete on TABLE For Each ROW Execute Prosedure reset_all_to_false()

      Function:

       Create Function reset_all_to_false() returns void Begin update table set active = false where active = true; end;

        Is this option suitable?
        from the beginning we set false
        update table set active = false where active = true
        and then set the desired line
        update table set active = true where условие
        Option 2
        update t set active = case when *условие true* then true else false end
        but this option is bad that the whole table will pass, you can add more conditions for limiting

        • Would approach, but it is necessary one request - sscream
        • @sscream can be wrapped in a transaction. Although one request is also possible. - D-side
        • one
          transaction as an option, but I also think that all the same can be a single request - sscream