Update Multiple Column in a Single Case Statement? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Update Multiple Column in a Single Case Statement?

Author

Topic

cbrochhagen
Starting Member

5 Posts

Posted - 2004-06-14 : 11:16:46

Is it possible to update multiple columns with a single case statement or does the case statement have to be repeated for each column? I need to update two columns that both use the same case criteria and would like to do it all in one case statement. Is this possible? Here’s an example of what I’m trying to do using two case statements:

UPDATE #wrkcd
SET
stopdate =
CASE
WHEN stopdate < @currentdate AND wustopcd = 'W' THEN stopdate
WHEN stopdate > @currentdate AND minstopdate < @currentdate THEN @currentdate
WHEN stopdate > @currentdate AND minstopdate > @currentdate AND minstopdate < stopdate THEN minstopdate
WHEN stopdate < minstopdate AND wustopcd <> 'W' THEN minstopdate
ELSE stopdate
END,
wustopcd =
CASE
WHEN stopdate < @currentdate AND wustopcd = 'W' THEN wustopcd
WHEN stopdate > @currentdate AND minstopdate < @currentdate THEN minwustopcd
WHEN stopdate > @currentdate AND minstopdate > @currentdate AND minstopdate < stopdate THEN minwustopcd
WHEN stopdate < minstopdate AND wustopcd <> 'W' THEN minwustopcd
ELSE wustopcd
END

Thanks for any assistance you can provide.

Curt

nr
SQLTeam MVY

12543 Posts

Posted - 2004-06-14 : 12:59:39

You have to do it as you have coded there.
You can simplify a bit at the expense of efficiency

update #wrkcd
set stopdate = case t2.type when 1 then stopdate when 2 then @currentdate when 3 then minstopdate when 4 then minstopdate else stopdate end ,
wustopcd = case t2.type when 1 then wustopcd when 2 then @minwustopcd when 3 then minwustopcd when 4 then minstopdate else wustopcd end ,
from #wrkcd t1
join (select t2.pk, type = CASE
WHEN stopdate < @currentdate AND wustopcd = 'W' THEN 1
WHEN stopdate > @currentdate AND minstopdate < @currentdate THEN 2
WHEN stopdate > @currentdate AND minstopdate > @currentdate AND minstopdate < stopdate THEN 3
WHEN stopdate < minstopdate AND wustopcd <> 'W' THEN 4
END
) t2
on t1.pk = t2.pk

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Billpl
Yak Posting Veteran

71 Posts

Posted - 2004-06-14 : 14:27:21

Depending on your application, you might consider going one step further and wrap nr's idea into a function in order to:
1) guarantee consistency 2) reuse 3) It's a business rule not a relational rule which would be best served with it's own identity.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources