Using Case within an Update - 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

General SQL Server Forums

New to SQL Server Programming

Using Case within an Update

Author

Topic

harrisw48
Starting Member

34 Posts

Posted - 2006-03-23 : 07:37:34

Im trying to do the following but get the error.
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.

The code is

update [table]
set [field 1] = (select (case [field 2]
when 'B' then 'Y'
when 'C' then 'N'
when 'N' then 'N'
when 'P' then 'Y'
else 'N'
end)
from [table]),
[field 3] = (select (case [field 2]
when 'B' then 'Y'
when 'C' then 'Y'
when 'N' then 'N'
when 'P' then 'N'
else 'N'
end)
from [table])

ANy ideas?

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-03-23 : 07:46:11

Just try

Update <Table> Set [Field 1]= Case [Field 2]When 'B' Then 'Y' When 'C' Then 'N'..... Else 'N' End ,
[Field 3] = Case [Field 2] = When 'B' Then 'Y' When 'C' Then 'N'..... Else 'N' End
From [Table]

Hope this should work for u ..

Sucess Comes to those who Believe in Beauty of their Dream..

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-03-23 : 07:50:27

try this:


update t1
set [field 1] = case [field 2]
					when 'B' then 'Y'
					when 'C' then 'N'
					when 'N' then 'N'
					when 'P' then 'Y'
					else 'N'
				end,
	[field 3] = case [field 2]
					when 'B' then 'Y'
					when 'C' then 'Y'
					when 'N' then 'N'
					when 'P' then 'N'
					else 'N'
				end 
from [table] t1