need help with case syntax

[pandatime]

Hi,

Not sure if what I'm trying to do is possible but:

update t1 a
set id =
	case
         when (isnumeric(reply) = 1) then reply
         else (select id from t2 b where b.email = a.reply)
    end

Basically, if "reply" is a number, set id to reply. Otherwise, run a query to find the value. It's this part I'm not sure is possible, and if so, what the syntax is.

Thanks

 

[SgtJarrow]

Have you tried to run this as a select instead of an update to see if you get the expected results??? I think this should work as you want...

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
VB.NET Programmer

 

[SDB15]

SET sometimes has issues when you don't put what you want in parens. Try enclosing the entire Case When in parens.

 

[PWise]

try

update t1
set id = case when (isnumeric(reply) = 1) then reply else t2.id end
from t1
inner join t2
on t1.reply=t2.email

 

[NastiaMurzin]

reply = 01;
go
select id from tablename where id = reply;
go
If you have any questions, feel free to ask.