CODEWITHSUNDEEP
sqlsql-serversql-update
DoArNa
DoArNa

Reputation: 532

Updating a table with different values for different conditions

I have a situation like this:

update lskinproperty
set frn_lskin_stateid = 2
where frn_lskinid IN (61209, 61208)

update lskinproperty
set frn_lskin_stateid = 3
where frn_lskinid IN (72670, 56916)

update lskinproperty
set frn_lskin_stateid = 4
where frn_lskinid IN (55451)

If I run this three queries, it will do what exactly what I want however I feel there it should be a better way to do this. Does SQL Server has anything like case or switch or something similar where you can say for case equal to x do this and for case equal to y do something else and so on?

Upvotes: 0

Views: 1372

Answers (2)

Gurwinder Singh
Gurwinder Singh

Reputation: 39457

You can filter for all the values of frn_lskinid at once and then use case to identify which value to assign for which frn_lskinid .

update lskinproperty
set frn_lskin_stateid = case 
        when frn_lskinid in (61209, 61208)
            then 2
        when frn_lskinid in (72670, 56916)
            then 3
        else 4
        end
where frn_lskinid in (72670, 56916, 61209, 61208, 55451)

The else here covers for the case when frn_lskinid is 55451 which is same as writing explicitly using another when:

update lskinproperty
set frn_lskin_stateid = case 
        when frn_lskinid in (61209, 61208)
            then 2
        when frn_lskinid in (72670, 56916)
            then 3
        when frn_lskinid = 55451
            then 4
        end
where frn_lskinid in (72670, 56916, 61209, 61208, 55451)

Upvotes: 1

Carsten Massmann
Carsten Massmann

Reputation: 28196

There probably isn't a real shortcut to doing this type of update. Using case forces you to supply the ids twice: once in the case clause and then again in the where clause. However, in case you have the values to change to in a tabular way, then you could do something like the following:

update tbl set frn_lskin_stateid=st
from (       select 61209 i,2 st
   union all select 61208,2
   union all select 72670,3
   union all select 56916,3
   union all select 55451,4 ) tvalues
inner join lskinproperty tbl on frn_lskinid=i;

In a real application tvalues would most likely be a table with your target values, but, of course, you can also supply the values in a sub-query with several union all lines like shown above.

For a demo see here: http://rextester.com/NDQGL19748

Upvotes: 1

Related Questions