I am using an INSERT
query that SELECT
s 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 null
s 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.
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