SWITCH function doesn't work in access sql

Question

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?

MatBailie · Accepted Answer · 2015-08-19T15:43:23.130

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.

I would also include a `count (*)` to the first `select - group by` because OP say a larger than expected number of `BIGINT` are appearing. — Juan Carlos Oropeza, Aug 19 '15 at 15:42
I dont know why, but adding `where in` row made query work as expected. — Rocketq, Aug 20 '15 at 06:55

SWITCH function doesn't work in access sql

1 Answers1