Update using a case statement - SQL Server Forums

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 using a case statement

Author

Topic

jung1975
Aged Yak Warrior

503 Posts

Posted - 2004-12-14 : 13:04:13

can I do something like this? I keep getting an error...

Update m

Set m.SubFirstname = case( when m.Relationship IN ('Child', 'Spouse') And a.p_asi_cd IN ('XX','XA') then m.SubFirstname = n.p_name_first
ELSE m.SubFirstname = x.p_name_first
END )

From #final m
Left Join ac_p_asi a on m.p_id = a.p_assoc_id
Left Join ac_p_name n on a.p_id = n.p_id
join #final X on m.p_id = x.p_id

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-12-14 : 13:07:08

Posting the error would be helpful.

Tara

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-12-14 : 14:24:17

removing the ( after the case and after the end maay help...

Set m.SubFirstname = case( when m.Relationship IN ('Child', 'Spouse') And a.p_asi_cd IN ('XX','XA') then m.SubFirstname = n.p_name_first
ELSE m.SubFirstname = x.p_name_first
END )

Go with the flow & have fun! Else fight the flow

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-12-14 : 14:44:17

remember -- CASE returns a value; nothing else, it cannot control flow of execution or anything. consider it a function.

you are trying to do your assignment in the CASE statement, which is not allowed. and also, it is redundant -- you've already indicated you are going to be assigning to the subFirstName column in the very beginning of the UPDATE statement.


update SomeTable
Set m.SubFirstname = 
     case when m.Relationship IN ('Child', 'Spouse') And 
               a.p_asi_cd IN ('XX','XA') 
          then 
               n.p_name_first 
          ELSE 
               x.p_name_first 
     END

- Jeff

C0dewarr10r
Starting Member

3 Posts

Posted - 2004-12-14 : 16:44:35

Is it possible to dynamically change the qualifier "=" to one of the following based on an input parameter - "<", ">", "<=", ...
-> dynamically modifying the where clause: date = @input param
date < @input param
date > @input param

update table
set name = 'Johnny'
where date case when @datequailifer = 'eq' then '= @input param
when @datequailifer = 'lt' then '< @input param
when @datequailifer = 'gt' then '> @input param
End

Thanks

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2004-12-14 : 17:48:01

C0de, I think you'll find Jeff Smith's answer over here: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=43717 helpful.

-----------------------------------------
Professional IT Solutions from Infoneering

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources