Need to convert Column data

Question

Post reply

Need to convert Column data

DMS11X

Mr or Mrs. 500

Points: 576
More actions
April 8, 2011 at 4:58 am

#249115

I have the following table (I imported from excel):
CREATE TABLE [dbo].[loc_test](
[ID] [int] IDENTITY(1,1) NOT NULL,
[assoc] [varchar](20) NULL,
[unitid] [varchar](20) NULL,
[lot] [varchar](10) NULL,
[address] [varchar](10) NULL,
[street] [varchar](50) NULL,
[city] [varchar](50) NULL,
[state] [varchar](2) NULL,
[zip] [varchar](10) NULL,
[Village] [varchar](10) NULL,
[O_first_name] [varchar](50) NULL,
[O_last_name] [varchar](50) NULL,
[R_first_name] [varchar](50) NULL,
[R_last_name] [varchar](50) NULL,
[Home_Tel_1] [varchar](20) NULL,
[O_Home_Tel_2] [varchar](20) NULL,
[R_Home_Tel_1] [varchar](20) NULL,
Currently in the 'assoc' column there are two different numbers 1 and 2, some records have 1's indicating property 1, and the others have 2's indicating property 2.
I want to convert the 1's to "SACRAMENTO"
I want to convert the 2's to "FOLSOM"
I have 4710 records in this table, 4232 of which are 1's
and 478 records which are 2's. I really dont want to have to manually enter the text "SACRAMENTO" or FOLSOM" for 4710 records :crazy:
Any Idea'rs?

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

i think this will do what you are asking:

UPDATE loc_test

SET assoc = CASE

WHEN assoc = '1'

THEN 'SACRAMENTO'

WHEN assoc = '2'

THEN 'FOLSOM'

END

WHERE assoc IN('1','2')

Edit: Nice Glasses!

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

DMS11X Mr or Mrs. 500 Points: 576 More actions · Answer 2

I ***BOW ***TO YOU NOW :hehe:

HUGE THANK YOU!!!

-Dane

DMS11X Mr or Mrs. 500 Points: 576 More actions · Answer 3

I also have a NULL Column in the same table, and want to change the data to "RESIDENCE"

I tried:

UPDATE dbo.LOCATION

SET [Type] = CASE

WHEN [Type] = 'null'

THEN 'RESIDENCE'

END

WHERE [Type] IN('null')

Zero (0) Rows Affected

???

Lowell SSC Guru Points: 323518 More actions · Answer 4

there's a difference between 'null'( a string literal) and the keyword NULL;

since you are not replacing more than one result, you don't need the CASE statement in this instance.

UPDATE dbo.LOCATION

SET [Type] = 'RESIDENCE'

WHERE [Type] IS NULL

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

DMS11X Mr or Mrs. 500 Points: 576 More actions · Answer 5

NEVER MIND

I figured it out:

(changed Type to L_Type) and...

UPDATE dbo.LOCATION

SET L_Type = CASE

WHEN L_Type IS null

THEN 'RESIDENCE'

END

WHERE L_Type IS null

PRESTO:w00t:

C.K.Shaiju SSCarpal Tunnel Points: 4228 More actions · Answer 6

CASE WHEN is not required. You can use Lowell's last script. Also you have a where condition. So all the data will be with NULL.

_____________________________________________
One ounce of practice is more important than tonnes of dreams