Updating Values in a table - 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

General SQL Server Forums

New to SQL Server Programming

Updating Values in a table

Author

Topic

bobshishka
Yak Posting Veteran

72 Posts

Posted - 2007-11-07 : 12:26:56

I have a table named 'Personal' that has a field that contains values that represent colors (ex: BLACK, BROWN, BLUE, etc.). How do I run an update statement to update the values to defined set. For example (BLACK = BLK, BLUE = BLU, etc.)?

Thanks

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2007-11-07 : 12:33:57

[code]UPDATE Personal
SET 'BLACK' = 'BLK'
,'BLUE' = 'BLU'
[/code]
etc

[Signature]For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
Learn SQL
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2007-11-07 : 12:36:17

wouldn't it be


UPDATE Personal
SET [ColumnName] = (Case when 'BLACK' then 'BLK' 
                         when 'BROWN' then 'BRN'
                         --etc
                    END)

bobshishka
Yak Posting Veteran

72 Posts

Posted - 2007-11-07 : 12:41:04

Using

UPDATE Person
SET [EYE_COLOR] = (Case when 'BLACK' then 'BLK'
when 'BROWN' then 'BRN'
END)

I get this error:

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

bobshishka
Yak Posting Veteran

72 Posts

Posted - 2007-11-07 : 12:53:08

OK, I got this to work:

UPDATE Person
SET HAIR_COLOR = 'BLK'
WHERE HAIR_COLOR = 'BLACK'

Now, how do I change multiple values with one command?

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2007-11-07 : 13:58:24

Oops my bad
[code]
UPDATE Person
SET [HAIR_COLOR] =
(Case [HAIR COLOR]
when 'BLACK' then 'BLK'
when 'BROWN' then 'BRN' --etc
END)

OR
[CODE]
UPDATE Person
SET [HAIR_COLOR] =
(Case when HAIR_COLOR = 'BLACK' then 'BLK'
when HAIR_COLOR = 'BROWN' then 'BRN' --etc
END)
[/CODE]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources