Skip to content
Advertisement

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

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:

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

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

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

The expected result of table1:

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.

Advertisement

Answer

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:

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

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

User contributions licensed under: CC BY-SA
10 People found this is helpful
Advertisement