Update column based on variable

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 2005 Forums

Transact-SQL (2005)

Update column based on variable

Author

Topic

vmon
Yak Posting Veteran

63 Posts

Posted - 2007-05-22 : 09:17:07

Can I do this? I want to update 1 of 5 columns based on a counter variable. I am having trouble getting it to work.

Thanks,
vmon

UPDATE A
SET A.(CASE
WHEN @Counter = 1 THEN Col1
WHEN @Counter = 2 THEN Col2
WHEN @Counter = 1 THEN Col3
WHEN @Counter = 2 THEN Col4
WHEN @Counter = 1 THEN Col5
END) = '5'

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-05-22 : 09:22:08

No. You can't
try this


UPDATE A
SET Col1 = case when @Counter = 1 then '5' else Col1 end,
    Col2 = case when @Counter = 2 then '5' else Col1 end,
    Col3 = case when @Counter = 1 then '5' else Col1 end,
    Col4 = case when @Counter = 2 then '5' else Col1 end,
    Col5 = case when @Counter = 1 then '5' else Col1 end

OR simple IF ... ELSE


IF @Counter = 1 UPDATE A SET Col1 = '5', Col3 = '5', Col5 = '5'
IF @Counter = 2 UPDATE A SET Col2 = '5', Col4 = '5'

vmon
Yak Posting Veteran

63 Posts

Posted - 2007-05-22 : 09:31:14

Oh cool. Thanks a bunch.
vmon

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources