Conditionals

[TamedTech]

Hello Guys,

I'm looking for a little advice on how to best form this query. I have three columns in a table, these are all 'int' datatypes and represent a 'state' for the record.

ConfigState int
EventState int
MessageState int

What I'm looking to achieve is a query which if the value of each colummn is 0 then leave it be, if its value is 1 then update it to 2 and the value is 2 leave it be.

I could do this using some simple application logic using an if statement in my application code but thought that I should probably sieze the oppertunity to further my knowledge of SQL and come up with a cleaner solution.

Can anyone suggest the best method to achieve this result? Will I need a nested select statement which looks at the current value for the column? or can that be avoided somehow?

Thanks for any advice,

Robert

 

[SQLDenis]

Use CASE

update SomeTable
set ConfigState = case ConfigState  when 1 then 2 else ConfigState   end,
EventState = case EventState  when 1 then 2 else EventState   end,
MessageState = case MessageState  when 1 then 2 else MessageState   end
where ConfigState =1
or EventState =1
or MessageState = 1

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[TamedTech]

Perfect!

Thanks a great deal Denis.

Robert