Geek Answers Handbook

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

I am struggling with an SQL statement.

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

Here is what I am doing 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

It works, but I don't like it. I would like to be able to do this once.

Is there any way to do this in SQL?

+4
source share
3 answers

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
+7
source

In some SQL products, you can simplify the syntax inside CASE by removing a few AND and OR in 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 there are a lot of rows in the table (for example, millions or billions), and only a small percentage is set to Active=1 , it will probably be more efficient to update 2 statements, similar to what you were from the very beginning, if you 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 ... ;
+2
source

Is it possible to use case expression inside update statement?

Sort of

 UPDATE Products set Active = CASE WHEN (Id_A = 1 AND Id_B = 1 AND Id_C = 1) THEN 1 WHEN (Id_A = 1 AND Id_B = 2 AND Id_C = 1) THEN 1 . . . ELSE 0 END
+1
source

More articles:


All Articles