Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
Can you not do this with some kind of case statement in your insert? –  RoKa Apr 9 '13 at 9:02
    
I don't really know how to. –  Yuri Apr 9 '13 at 9:07
    
OK. How are you currently performing the transfer from one table to another? If you could post the statement I can amend it and provide an answer / explanation for you. –  RoKa Apr 9 '13 at 9:08
    
INSERT INTO table2 (name, state, dob) SELECT name, state, dob FROM table1 That's an excerpt of it. These are for tables with matching data types. –  Yuri Apr 9 '13 at 9:13

1 Answer 1

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.

share|improve this answer
    
Thank you, Roka! It is MYSQL though, do you reckon that format would work. –  Yuri Apr 9 '13 at 9:20
    
Yes, it should work. You can find details of the mySQL CASE syntax here : dev.mysql.com/doc/refman/5.0/en/case.html –  RoKa Apr 9 '13 at 9:21
    
Cheers, Roka, you're awesome! –  Yuri Apr 9 '13 at 9:23
    
Thanks. 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 –  RoKa Apr 9 '13 at 9:28
    
@yuri: If the solution is correct and accepted, remember to mark this question as "Answered". –  RoKa Apr 9 '13 at 9:46

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.