Mysql – How to copy data from table to another and automatically change the values to match the other’s data type msql

MySQL

I want to copy data from table column to another and automatically change the values to match the other's data type.

That is; in one table, a column is "Gender" and the values are Male & Female for every row. Now, while transferring it to the other table, I want "Male" to change to "1", "Female" to "2". I can't do it manually cause it's a huge table.

Best Answer

Based on the information and SQL supplied, you would need to amend the statement as follows:

insert into table2 (
        name, state, dob, gender)
select 
    name, state, dob, 
    case gender 
        when 'Male' then 1
        when 'Female' then 2
     end 
from table1

Remember that NULL values will obviously be ignored.

MySQL CASE syntax

Since you will always only have one of two values in this query, you can also of course use the IF operator, depending of course on how you want to handle NULL values.

select name, state, dob, if(gender='Male',1,2)
from table1

or

select name, state, dob, if(gender='Male',1,if(gender='Female',2))
from table1
Related Question