SQL UPDATE all rows, except some rows, in table with composite primary key

Question

I'm struggling with a SQL statement.

I want to update all rows except some, in a table with a composite primary key.

This is what I do now:

UPDATE Products SET Active = 0

_

UPDATE Products SET Active = 1
WHERE (Id_A = 1 AND Id_B = 1 AND Id_C = 1) OR
      (Id_A = 1 AND Id_B = 2 AND Id_C = 1) OR
      (Id_A = 5 AND Id_B = 8 AND Id_C = 3) OR
       .
       .
       .
       etc

This works, but I don't like it. I would like to be able to do it one go.

Is there some way to do this in SQL?

Answer 1

You mean something like:

UPDATE Products SET Active = CASE WHEN
      (Id_A = 1 AND Id_B = 1 AND Id_C = 1) OR
      (Id_A = 1 AND Id_B = 2 AND Id_C = 1) OR
      (Id_A = 5 AND Id_B = 8 AND Id_C = 3) OR
       .
       .
       .
       THEN 1 ELSE 0 END

Answer 2

In some SQL products, you can further simplify the syntax inside CASE, removing the multiple AND and OR to a simple IN:

UPDATE Products 
SET Active = CASE WHEN
      (Id_A, Id_B, Id_C) IN
      ( (1, 1, 5), (1, 2, 1), (5, 8, 3) ... ) 
             THEN 1 ELSE 0 
             END ;

---

Another thing to consider is that if the table has a lot of rows (like millions or billions) and only a small percentage is set to Active=1, it will probably be more efficient to have the updating done is 2 statements, similar to what you had from the beginning, assuming you a have an index on (Active) (or a partial index on (Active=1) ):

UPDATE Products SET Active = 0 WHERE Active = 1 ;

UPDATE Products SET Active = 1 WHERE ... ;