CASE Help - 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

SQL Server 2005 Forums

Transact-SQL (2005)

CASE Help

Author

Topic

justjohno
Starting Member

23 Posts

Posted - 2007-11-23 : 16:58:04

I need to update a column in several rows that match a criteria, but want to leave the others alone. I got the CASE statement to work, but it replaces existing data with a NULL in the entire column for the rows that don't match the criteria if ELSE statement is ommited. Can this be done with CASE or am I missing something.

Thanks in advance for any help.

John O

justjohno
Starting Member

23 Posts

Posted - 2007-11-23 : 23:51:30

I'm just learning SQL and trying to survice my first explaination probably didnt make sense. Let me try again. For the sake of this example I have a table described below.

7 5 2
7 2 2
7 4 5
7 1 3

If I excute the simple case statement at the bottom of this post it returns this result set. I dont want the other values replaced with a NULL. Is there a way to keep the existing values? Should I be using CASE?

7 5 Null
7 2 Value2
7 4 Null
7 1 Value1

UPDATE table
SET COL3 =
CASE
WHEN COL2 = '1' Then 'Value1'
WHEN COL2 = '2' Then 'Value2'
--ELSE 'Home'
END;

John O

kutumbarao
Starting Member

13 Posts

Posted - 2007-11-24 : 00:02:25

Hi Please try with following query

UPDATE table
SET COL3 =
CASE
WHEN COL2 = '1' Then 'Value1'
WHEN COL2 = '2' Then 'Value2'
ELSE COL3
END;

justjohno
Starting Member

23 Posts

Posted - 2007-11-24 : 00:50:24

kutumarao,

PERFECT! Thank you so much. Didn't know I could reference back to the column.

John O

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources