Update different columns based on conditions

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

Transact-SQL (2008)

Update different columns based on conditions

Author

Topic

LOOKUP_BI
Constraint Violating Yak Guru

295 Posts

Posted - 2011-03-25 : 15:45:13

Hi,

I have a table V which has 4 columns ID,Value_1, Value_2 & Value_3.
I have a table S which has 3 columns ID,Value,Type
Table S, Type column has values [1,2 & 3]
The joining column between both is ID.

Need to Update column Value_1, Value_2 & Value_3 on Table V
based on column Type of S Table.
Set Value_1= S.Value if S.Type=1
Set Value_2= S.Value if S.Type=2
Set Value_3= S.Value if S.Type=3

Column Value_2 & Value_3 would be NULL if S.Type is 1 and so on..

Update v
if(S.Type=1)
begin
set Value_1= S.Value
end
if(S.Type=2)
begin
set Value_2= S.Value
end
if(S.Type=3)
begin
set Value_3= S.Value
end

name = s.name,
group=s.group,
from V
INNER JOIN S ON V.ID=S.ID

name & group columns are other columns to be updated as well.

How should I write and Update statment for table V ?
I get error message
"Incorrect syntax near the keyword 'if'." Line 2
"Incorrect syntax near '='." Line 4 , Line 8 & Line 12

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-03-25 : 15:45:46

Use CASE instead.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

LOOKUP_BI
Constraint Violating Yak Guru

295 Posts

Posted - 2011-03-25 : 15:58:18

Hi Tara,

Will It be like this ?

Update v
set Value_1=CASE
WHEN(s.Type=1 ) THEN S.Value
end
set Value_2=CASE
WHEN(s.Type=2 ) THEN S.Value
end
set Value_3=CASE
WHEN(s.Type=3 ) THEN S.Value
end
name = s.name,
group=s.group,
from V
INNER JOIN S ON V.ID=S.ID

I get an error message "Incorrect syntax near '='."Line 5

LOOKUP_BI
Constraint Violating Yak Guru

295 Posts

Posted - 2011-03-25 : 16:06:55

Got It to work !!

Update v
set Value_1=CASE
WHEN(s.Type=1 ) THEN S.Value
end,
Value_2=CASE
WHEN(s.Type=2 ) THEN S.Value
end,
Value_3=CASE
WHEN(s.Type=3 ) THEN S.Value
end,
name = s.name,
group=s.group,
from V
INNER JOIN S ON V.ID=S.ID

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-03-25 : 16:09:31

quote:
Originally posted by LOOKUP_BI

Got It to work !!

Update v
set Value_1=CASE
WHEN(s.Type=1 ) THEN S.Value
end,
Value_2=CASE
WHEN(s.Type=2 ) THEN S.Value
end,
Value_3=CASE
WHEN(s.Type=3 ) THEN S.Value
end,
name = s.name,
group=s.group,
from V
INNER JOIN S ON V.ID=S.ID

I'm not 100% sure what you want to have as a result, but without supplying an ELSE on each CASE expression you will update some or all of the values to NULL. WHich may be what you want to have happen.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-03-25 : 16:10:18

set
Value_1 = case when ... then ... end ,
Value_2 = case when ... then ... end ,
Value_3 = case when ... then ... end ,
name = s.name,
group = s.group NO COMMA HERE!
from ...

No, you're never too old to Yak'n'Roll if you're too young to die.

LOOKUP_BI
Constraint Violating Yak Guru

295 Posts

Posted - 2011-03-25 : 16:15:14

Yes Lamprey, I would add the Else Condition.Thanks

Webfred, thanks for pointing out my typo mistake

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources