Update with a case - 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 2005 Forums

Transact-SQL (2005)

Update with a case

Author

Topic

rookie_sql
Constraint Violating Yak Guru

443 Posts

Posted - 2009-12-17 : 04:36:46

Hi i need to do a update using a case.

my update looks like this

update tbl
set b_code = CASE when b_h = 'b' and o_type = 1 then 1 else 0 end

i'd like to know if the syantx is correct.
thanks

prakum
Starting Member

16 Posts

Posted - 2009-12-17 : 04:42:57

its correct only....are you getting any error while executing the above statements.....

Praveen Kumar

Sachin.Nand

2937 Posts

Posted - 2009-12-17 : 04:43:03

No
You will have to use 2 case statements to fulfil the condition.
update tbl
set b_code = CASE b_h when 'b'
CASE o_type when 'b' then 1 else 0 end
END

PBUH

rookie_sql
Constraint Violating Yak Guru

443 Posts

Posted - 2009-12-17 : 04:51:24

Nope not getting a error i was not sure if the code was doing it correctly or not. so should i use Idera example below of the 2 case ?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-12-17 : 04:59:45

For clarity you need to post some sample data with expected result

Madhivanan

Failing to plan is Planning to fail

rookie_sql
Constraint Violating Yak Guru

443 Posts

Posted - 2009-12-17 : 06:31:10

update tbl
set b_code = CASE when b_h = 'b' and o_type = 1 then 1 else 0 end

Here is the expected results

b_h o_type b_code
b 0 0
b 1 1
b 0 0

rajdaksha
Aged Yak Warrior

595 Posts

Posted - 2009-12-17 : 06:36:08

hi

Where sample data?

-------------------------
R...

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2009-12-17 : 10:47:04

You don't need a forum to check syntax. There's a parse button in Management Studio for that. Anyway...

declare @tbl table (b_h varchar(9), o_type int, b_code int)
insert @tbl (b_h, o_type)
          select 'b', 0
union all select 'b', 1
union all select 'b', 0
union all select 'a', 1

update @tbl set b_code = CASE when b_h = 'b' and o_type = 1 then 1 else 0 end

select * from @tbl

Ryan Randall - Yak of all trades
Solutions are easy. Understanding the problem, now, that's the hard part.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources