CASE in UPDATE that determines column to update?

Question

How can I use a CASE that will affect which column will be updated?

UPDATE TABLE1
    CASE WHEN [Status] = 'Alpha' THEN AlphaStatus = Status

Using the solution here, I tried something like the following, but it only updates 1 column.

DECLARE @Table TABLE
(
    ID int,
    AlphaStatus varchar(10),
    BetaStatus varchar(10),
    GammaStatus varchar(10)
)

insert into @table (id) select 1
insert into @table (id) select 2


Declare @Values Table
(
 ID varchar(20),
 [Group] varchar(20),
 [Status] varchar(20)
)

insert into @Values ( ID, [Group], [Status]) select 1, 'Alpha', 'ENABLED'
insert into @Values ( ID, [Group], [Status]) select 1, 'Beta', 'ENABLED'
insert into @Values ( ID, [Group], [Status]) select 1, 'Gamma', 'DISABLED'
insert into @Values ( ID, [Group], [Status]) select 2, 'Alpha', 'ENABLED'
insert into @Values ( ID, [Group], [Status]) select 2, 'Gamma', 'ENABLED'
insert into @Values ( ID, [Group], [Status]) select 2, 'Beta', 'ENABLED'


update @Table 
set
AlphaStatus = (case when [Group] = 'Alpha' then [Status] else AlphaStatus end),
BetaStatus = (case when [Group] = 'Beta' then [Status] else BetaStatus end),
GammaStatus = (case when [Group] = 'Gamma' then [Status] else GammaStatus end)
from @Table t inner join @Values r 
on r.id = t.ID

select *From @Table

FutbolFan · Answer 1 · 2015-09-02T18:45:04.387

You could write this in two separate ways:

First Method:

UPDATE t
SET t.AlphaStatus = coalesce(ra.[Status], t.AlphaStatus)
    ,t.BetaStatus = coalesce(rb.[Status], t.BetaStatus)
    ,t.GammaStatus = coalesce(rg.[Status], t.GammaStatus)
FROM @Table t
LEFT JOIN @values ra ON ra.id = t.ID
    AND ra.[group] = 'Alpha'
LEFT JOIN @values rb ON rb.id = t.ID
    AND rb.[group] = 'Beta'
LEFT JOIN @values rg ON rg.id = t.ID
    AND rg.[group] = 'Gamma';

Second Method:

update t
set t.AlphaStatus = coalesce(r.[Status],t.AlphaStatus) 
from @Table t 
left join @Values r 
on r.id = t.ID
and [group] = 'Alpha';


update t
set t.BetaStatus = coalesce(r.[Status],t.BetaStatus) 
from @Table t 
left join @Values r 
on r.id = t.ID
and [group] = 'Beta';

update t
set t.GammaStatus = coalesce(r.[Status],t.GammaStatus) 
from @Table t 
left join @Values r 
on r.id = t.ID
and [group] = 'Gamma';

select * from @Table

SQL Fiddle Demo

score 2 · Accepted Answer · answered Sep 02 '15 at 17:44

By separating each field into a different join, you can do it in a single update.

update t 
set
    AlphaStatus = ISNULL(rA.[Status], AlphaStatus),
    BetaStatus = ISNULL(rB.[Status], BetaStatus),
    GammaStatus = ISNULL(rG.[Status], GammaStatus)
from @Table t
left join @Values rA on rA.id = t.ID AND rA.[Group] = 'Alpha'
left join @Values rB on rB.id = t.ID AND rB.[Group] = 'Beta'
left join @Values rG on rG.id = t.ID AND rG.[Group] = 'Gamma'

Brian Stork · Answer 3 · 2015-09-02T19:29:47.363

2

As a single update without a bunch of left joins, you could break your Values table out into a pivot table (inside a common table expression) and join on like column names:

;with CTE
as (
    select [ID]
         , [Alpha] as AlphaStatus
         , [Beta]  as BetaStatus
         , [Gamma] as GammaStatus
    from (
          select [ID]
               , [Group]
               , [Status]
            from @Values
         ) Source
    pivot (
           min(Status)
           for [group] in ([Alpha],[Beta],[Gamma])
          ) as pvt
   )
update @Table
   set AlphaStatus = coalesce(CTE.AlphaStatus,T.AlphaStatus)
     , BetaStatus  = coalesce(CTE.BetaStatus,T.BetaStatus)
     , GammaStatus = coalesce(CTE.GammaStatus,T.GammaStatus)
  from @Table T
  join CTE
    on CTE.[ID] = T.[ID]

edited Sep 02 '15 at 19:29

answered Sep 02 '15 at 17:55

Brian Stork

945
2
8
14

The problem with this is that the @Values table may not contain data for every ID-Status combination, so you'll be updating values to NULL incorrectly. – ErikE Sep 02 '15 at 18:17
Good point. Didn't see it in the OP, but I've updated the SET section to handle an imbalance in @Values. Thanks! – Brian Stork Sep 02 '15 at 19:30

CASE in UPDATE that determines column to update?

3 Answers3