CODEWITHSUNDEEP
sqlsql-server-2008
goartak
goartak

Reputation: 31

Alter table add column with data populated from another column

I'm trying to add a column to a table which needs to take a varchar value from an existing column in the table and convert it into an int in an if/then format.

example: 

if size = d then size_int = 1
else if size = f then size_int = 2
else if size = t then size_int = 3
else if size = s then size_int = 4
else if size = m then size_int = 5
else if size = l then size_int = 6
else if size = h then size_int = 7
else if size = g then size_int = 8
else if size = c then size_int = 9

If there is an easier way to do this by first adding the column then altering it that would work too.

Upvotes: 3

Views: 2672

Answers (1)

Chandu
Chandu

Reputation: 82943

Try this:

ALTER TABLE <YOUR_TABLE> ADD size_int INT;
UPDATE <YOUR_TABLE> SET size_int = 
CASE size 
    WHEN 'd' THEN 1
    WHEN 'f' THEN 2
    WHEN 't' THEN 3
    WHEN 's' THEN 4
    WHEN 'm' THEN 5
    WHEN 'l' THEN 6
    WHEN 'h' THEN 7
    WHEN 'g' THEN 8
    WHEN 'c' THEN 9
    ELSE NULL
END

Upvotes: 3

Related Questions