CASE in Update

[wuwang]

Hi,

Can I use the CASE in update syntex?
I got error message in my query --

update table1 set
case column5
when null then column5 = column5 + '-- testing'
else
comments = 'testing'
end
where primary_key = 1234

Could you tell me where is the problem? Thank you......

 

[fluteplr]

you can use the case statment to change the data being input into a field not which field is being updated.

Books online has extensive example of how to use the case statement

 

[tlbroadbent]

You must name the columns outside of the Case statement.

I don't understand what you are trying to do do. For example, you set column5 + '-- testing' when column5 is Null. Did you really mean Not Null? My suggested solution assumes that you did mean Not Null.

Update table1 set
column5 =
Case column5
When Null then 'testing'
Else column5
End,
Comments =
Case column5
When null then Comments
Else Comments + ' -- testing'
End
Where primary_key = 1234

Regardless of your requirement, this should give you an idea of how to proceed. Terry

http://members.home.net/tlbroadbent/prog.htm

The reason why worry kills more people than work is that more people worry than work. - Robert Frost

 

[wuwang]

I apologize for giving you unclear code.
Your code works fine. Thank you