SQL Question
Converting varchar column values into integer in MySql
We have a table in MySql , in that table we have a varchar column called 'type'.
Table having around 50 million records.
select distinct log_type from logs limit 3;
+-------------------+
| type |
+-------------------+
| EMAIL_SENT |
| EMAIL_OPEN |
| AD_CLICKED |
+-------------------+
as of now we have 70 distinct types. In future we will have more types.
now we want to convert this varchar column into integer column.
Just we want to update like following
for EMAIL_SENT I will use 1
for EMAIL_OPEN I will use 2
and so on
and then result column will be like following
+-------------------+
| type (int) |
+-------------------+
| 1 |
| 2 |
| 3 |
+-------------------+
We are also changing few other columns so we are creating new table and load all values from existing table like following
insert into new_table select * from old_table
how can we do this varchar to int conversion while loading values into new table.
Answer Source
Just use a giant case:
insert into new_table(LogTypeId, . . .)
select (case logtype
when 'EMAIL_SENT' then 1
when 'EMAIL_OPEN' then 2
. . .
end), . . .
from logs;
Your strategy of creating a new table is a much better idea than trying to update the existing one.