With the code below, I'm trying to update table K_tables
. The column ColumnType
doesn't have zero or null values in it, but after I run this script I get a lot of null valued rows, and the number of rows with certain values (such as BIGINT
) is larger than expected.
update K_tables set ColumnType = Switch(
ColumnType = 'I8' , 'BIGINT',
ColumnType = 'I' , 'INTEGER',
ColumnType = 'CF' , 'CHARACTER',
ColumnType = 'D' , 'DECIMAL',
ColumnType = 'I1' , 'BYTEINT',
ColumnType = 'I2' , 'SMALLINT',
ColumnType = 'DA' , 'DATE',
ColumnType = 'TS' , 'TIMESTAMP',
ColumnType = 'CV' , 'VARCHAR'
);
Is there something wrong with my query?
What are the results of this?
My guess is that there are values in there not mentioned in your SWITCH statement? These will be forced to NULL as the SWITCH gave no result.
I would simply add a WHERE clause...
This will mean that you only update rows that fit your criteria, and leave all the other rows alone.