Update table using case statement?? 1

[awise]

I have the following sql script that I am trying to use to update a field:

update rm00101
case a.code
when 1 set b.phone3='0'
when 5 set b.phone3='A'
when 3 set b.phone3='U'
when 4 set b.phone3='U'
when 0 set b.phone3='1'
end as b.phone3
from ageverifyresult a, rm00101 b
where a.custnmbr = b.custid

It fails on the second line, right after I start the case statement.

First question - is it possible to update a table's field using a case statement in this manner? If so, what is the proper syntax to accomplish this? If not, what is the recommended alternative?

Thanks,

Andrew

 

[jbenson001]

what do you mean it fails? Does it not compile, or does if fail during exectution?

 

[micha123]

Try to add this line:

update rm00101
SET FIELD_NAME =
case a.code
when 1 set b.phone3='0'
when 5 set b.phone3='A'
when 3 set b.phone3='U'
when 4 set b.phone3='U'
when 0 set b.phone3='1'
end as b.phone3
from ageverifyresult a, rm00101 b
where a.custnmbr = b.custid

This is what you've missed...

M.

 

[awise]

This is the exact message when I execute the query:

Server: Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'case'.

 

[gmmastros]

I'm not real sure about the syntax, but try this instead.

update b 
Set b.Phone3 =  case a.code
                when 1 then '0'
                when 5 then 'A'
                when 3 then 'U'
                when 4 then 'U'
                when 0 then '1'
                end 
from ageverifyresult a, rm00101 b
    where a.custnmbr = b.custid

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[awise]

Micha123,

After I tried your addition, it got past line 2, but then gave the following on "set" in line 4:

Server: Msg 156, Level 15, State 1, Line 4
Incorrect syntax near the keyword 'set'.

 

[awise]

George, many thanks that worked flawlessly.

Andrew

 

[gmmastros]

glad to help.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[micha123]

Yeah... I missed the "set" in the case too....
(Need my glasses...?)

Mmmm...