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?

Why don't you put the rows that shouldn't be updated instead? What are the exceptions? — aF., Aug 30 '12 at 13:17
What database system, and which version?? *SQL* is just the *Structured Query Language* - a language used by many database systems, but not a a database product... it would really help to know what **database system** you're using.... — marc_s, Aug 30 '12 at 13:18

score 7 · Accepted Answer · answered Aug 30 '12 at 13:18

7

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

answered Aug 30 '12 at 13:18

Alex K.

171,639
30
264
288

I think he wants to reduce the number of restrictions and not putting them elsewhere. – aF. Aug 30 '12 at 13:20
Ah I was thinking he wanted 0/1 in a single update based on a condition – Alex K. Aug 30 '12 at 13:21
+1 then, but @JonList with your query you do it all in once as well, or are you making a lot of updates? – aF. Aug 30 '12 at 13:23
I do two updates (both queries from the initial post). I only know which products needs to be activate, so i deactivate everything in the db and then activate the ones i know needs to be active. – Jon List Aug 30 '12 at 13:29

ypercubeᵀᴹ · Answer 2 · 2012-08-30T13:43:01.813

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

unfortunately, my mssql does not like this one. I like the cleanness of it though — Jon List, Aug 30 '12 at 13:34

score 1 · Answer 3 · answered Aug 30 '12 at 13:21

Can you use a case expression inside an update statement?

Something like

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

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

3 Answers3