Consolidating an SQL UPDATE statement using same table and multiple variables and WHERE IN clause

Question

I have a single table table1 where I am trying to update one column completed to certain values based on id. Because the table remains the same as well as the updated column and WHERE clause field, I have been looking into the potential of consolidating the SQL statements.

I have a table1 like this:

| id | completed |
| -- | --------- |
| 1  | NULL      |
| 2  | NULL      |
| 3  | NULL      |
| 4  | NULL      |
| 5  | NULL      |
| 6  | NULL      |
| 7  | NULL      |
| 8  | NULL      |
| 9  | NULL      |
| 10 | NULL      |
| 11 | NULL      |
| 12 | NULL      |

Here is an example of what I currently have as standard: (it continues on for another 13 UPDATE statements)

UPDATE table1 SET completed = 25
WHERE id IN (1, 2, 3, 4);

UPDATE table1 SET completed = 27
WHERE id IN (5, 6, 7, 8);

UPDATE table1 SET completed = 28
WHERE id IN (9, 10, 11, 12);

I have tried something like: (though I know it's not right)

UPDATE table1 (
     SET completed = 25 WHERE id IN (1, 2, 3, 4),
     SET completed = 27 WHERE id IN (5, 6, 7, 8),
     SET completed = 28 WHERE id IN (9, 10, 11, 12)
);

ASSUMPTIONS: id is unordered in the real table; skips and does not correlate to completed number order

The expected result of table1:

| id | completed |
| -- | --------- |
| 1  | 25        |
| 2  | 25        |
| 3  | 25        |
| 4  | 25        |
| 5  | 26        |
| 6  | 26        |
| 7  | 26        |
| 8  | 26        |
| 9  | 27        |
| 10 | 27        |
| 11 | 27        |
| 12 | 27        |

QUESTION: Would there be an even more consolidated version given the above? Or is the first code the most efficient?

I have also looked around on stackoverflow and documentation but did not see something like this addressed directly.

Joel Coehoorn · Accepted Answer · 2022-04-27 21:51:21Z

1

UPDATE table1 
 SET completed = CASE WHEN id IN (1, 2, 3, 4) THEN 25
                      WHEN id IN (5, 6, 7, 8) THEN 27
                      WHEN id IN (9, 10, 11, 12) THEN 28
                      ELSE completed END

This will update every row in the table, even if potentially sets most of them back to the original values.

I also see this:

id is unordered in the real table; skips and does not correlate to completed number order

Given that, you might want to actually create a table (named something like CompletionMap) to track these mappings. Something like this:

id	status
1	25
2	25
3	25
4	25
5	27
6	27
7	27
8	27
9	28
10	28
11	28
12	28

At this point, you no longer even really need to update the original table, because you already have this data in the DB. But if you really wanted to, it would look like this:

UPDATE t1
 SET t1.completed = m.status
FROM t1
INNER JOIN CompletionMap m on m.id = t1.ID

If you don't want to do that, you can also use a Table-Value Constructor in a similar way:

UPDATE t1
 SET t1.completed = map.status
FROM t1
INNER JOIN (VALUES
    (1, 25),
    (2, 25),
    (3, 25),
    (4, 25),
    (5, 27),
    (6, 27),
    (7, 27),
    (8, 27),
    (9, 28),
    (10, 28),
    (11, 28),
    (12, 28)
 ) as map(id, status) on map.id = t1.ID

This has the additional advantage of targeting the UPDATE better to only impact the desired rows.

edited Apr 27, 2022 at 21:51

answered Apr 27, 2022 at 21:30

Joel Coehoorn

418k114 gold badges582 silver badges820 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

tylerjames Over a year ago

I think what you have suggested makes sense - it helped to look at it from another perspective. I wouldn't need to create a table since this is a fix for an error and I'm just requesting with script.

Joel Coehoorn Over a year ago

@tylerjames I did just append one additional option to the end of the answer.

tylerjames Over a year ago

thanks! I think the first applies the best since the real table update is around 5000 values with multiple cases and trying to compile to maps is too much

Collectives™ on Stack Overflow

Consolidating an SQL UPDATE statement using same table and multiple variables and WHERE IN clause

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related