Convert boolean column to integer

Question

In mysql I have a CHAR(1) column containing 'T' and 'F' for true and false. I want to convert this in a single SQL statement to a TINYINT(1) column with 1 and 0. How can I do this without creating a new column? If I do

alter table my_table modify my_column tinyint;

then I presume automatic conversion will result in all my rows being replaced with 0. How can I specify a conversion table during column type modification?

sagi · Accepted Answer · 2016-08-07 13:24:22Z

4

With one command? I doubt its possible, without creating a new column? You can try this:

You can do it in two steps - first convert everything to numbers , and then try modifying the column(untested) :

UPDATE my_table t
SET t.my_column = CASE WHEN t.my_column = 'T' THEN '1'
                                              ELSE '0'
                  END;

alter table my_table modify my_column tinyint;

answered Aug 7, 2016 at 13:24

sagi

40.6k7 gold badges66 silver badges87 bronze badges

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

Collectives™ on Stack Overflow

Convert boolean column to integer

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest