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

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.

CodePudding user response：

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.