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 |
+-------------------+
+-------------------+
| type (int) |
+-------------------+
| 1 |
| 2 |
| 3 |
+-------------------+
insert into new_table select * from old_table
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.