[SOLVED] SWITCH function doesn't work in access sql

SWITCH function doesn't work in access sql

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?

Solution

What are the results of this?

SELECT ColumnType, COUNT(*) FROM K_tables GROUP BY ColumnType

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...

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'
                )
WHERE
    ColumnType IN ('I8','I', 'CF', 'D', 'I1', 'I2', 'DA', 'TS', 'CV')
;

This will mean that you only update rows that fit your criteria, and leave all the other rows alone.