Alter table add column with data populated from another column

Question

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.

If those two columns are going to retain that relationship, you're breaking normal form (and storing derived data) with this layout. It would be preferable to derive the size_int in a VIEW or store the size to size_int mappings in a lookup table. — Larry Lustig
– Larry Lustig, Commented Jan 25, 2011 at 4:21

Chandu · Accepted Answer · 2011-01-25 04:19:13Z

3

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

answered Jan 25, 2011 at 4:19

Chandu

83.2k19 gold badges136 silver badges136 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

goartak Over a year ago

the purpose of the derived data was simply to do a quick update to the table then drop the size col and continue forward using only size_int. i thought this would be the easiest way to do it in order to write a select statement to return the sizes within a range i.e. =3, or between 4 and 6, <5 etc.

Collectives™ on Stack Overflow

Alter table add column with data populated from another column

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related