update table with case statement

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

General SQL Server Forums

New to SQL Server Programming

update table with case statement

Author

Topic

Teachme
Starting Member

45 Posts

Posted - 2007-08-31 : 10:41:04

can plz anyone tell me how to fix the following update script. thanks

Update table
set rating =
case
when rating in
(select code from tbl_Codes
where code = '0')
then Rating = '0'

when Rating in
(select code from tbl_Codes
where code = '1')
then InternalRating = '1'
else rating
end

mwjdavidson
Aged Yak Warrior

735 Posts

Posted - 2007-08-31 : 11:06:22

Post your table structure, some example data and the expected result. I could point out the syntax errors, but it wouldn't necessarily give you a statement that did what you wanted.

Mark

Teachme
Starting Member

45 Posts

Posted - 2007-08-31 : 11:27:55

table rating has rating field of varchar
data: nb,abc,t1,t2, (example)

code table has fileds valid and invalid of varchar
data: valid invalid
0 nb
0 abc
1 t1
1 t2

the result in rating table should be
data:0 0 1 1 (values changed/updated)

i just needed to know how do i use the case statement that way,
it gives me error where i have "then rating=value"..

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-08-31 : 11:40:39

update R
Set R.col = C.ValId
From Rating R
Join Code C ON R.Col1 = C.col2

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

mwjdavidson
Aged Yak Warrior

735 Posts

Posted - 2007-08-31 : 12:31:59

The error was due to


SET column = CASE 
                WHEN column = value1
                THEN column = value2
             ...

as opposed to


SET column = CASE
                WHEN column = value1
                THEN value2
             ...

However, your cases seem essentially to be saying

if column = '0' update it to '0'!

It's still not entirely clear. Does rating has one column or multiple columns?

Mark

smithani
Starting Member

42 Posts

Posted - 2007-08-31 : 13:42:06

Update table
set rating =
case
when rating in
(select code from tbl_Codes
where code = '0')
then '0'

when Rating in
(select code from tbl_Codes
where code = '1')
then '1'
else null
end

well since you alreday have rating =, you dont need to specify the rating= agian in your then statements.I had problems in my sql because of that

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources