In MySQL, how do I insert 1 when string value is 'true', 0 when false, while preserving nulls

StormeHawke picture StormeHawke · Mar 28, 2013 · Viewed 12.7k times · Source

I am using an INSERT query that SELECTs from a plain-text database into a more query-friendly database. The following is simplified a lot to prevent confusion with unnecessary information, if I've simplified too far for clarity please let me know and I will amend.

Source data appears as follows:


    |    id (integer)    |    SomeCol (varchar)    |    BooleanCol (varchar)   |    
    ----------------------------------------------------------------------------
    |    1               |    'String Data'        |    'true'                 |
    |    2               |    'Other Data'         |    'false'                |

The target table needs to look as follows after my INSERT query:


    |    id (integer)    |    SomeCol (varchar)    |    BooleanCol (tinyint(1))   |    
    -------------------------------------------------------------------------------
    |    1               |    'String Data'        |    1                         |
    |    2               |    'Other Data'         |    0                         |

I'm looking for something that would work more or less like this, borrowing a bit from java syntax:


    INSERT INTO target(SomeCol, BooleanCol) 
    SELECT SomeCol, (BooleanCol IS NOT NULL ? (BooleanCol == 'true' ? 1 : 0) : null) 
    FROM source

Note that the java syntax provided as an example of what I want is intended to preserve nulls as needed.

Also, while I'm here, if you happen to know how SELECT the current time stamp from MySQL that would also be helpful.

Answer

Vatev picture Vatev · Mar 28, 2013

Mysql has an IF operator which is similar to the ?: operator in most languages. IF(cond, val_if_true, val_if_false)

INSERT INTO target(SomeCol, BooleanCol) 
SELECT SomeCol, IF(BooleanCol IS NOT NULL , IF(BooleanCol = 'true' , 1 , 0) , null) 
FROM source