Issue with cast function in update query

Here is a simple table and test data that I will use to demonstrate my issue:

create table foo(
  id INTEGER AUTO_INCREMENT PRIMARY KEY,
  orderpos int not null
);

insert into foo (orderpos) values (1);
insert into foo (orderpos) values (2);
insert into foo (orderpos) values (3);
insert into foo (orderpos) values (4);
insert into foo (orderpos) values (5);

I would like to update some fields with a query like this:

update foo
set orderpos = 
        CAST( 
        CASE 
          WHEN id = 2 THEN 4
          WHEN id = 3 THEN 8
         END
         AS INTEGER 
        )  
where id in(2, 3);

However I get the error

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INTEGER 
        )  
where id in(2, 3)'

I understand that by removal of a CAST query will work but I would like to understand why is this operation not permitted?

MySQL is 5.6 Here is the link to fiddle http://sqlfiddle.com/#!9/420d7f

Answer

It’s true you don’t need to use CAST() in this case.

But for the record, the error is because you’re using INTEGER in a position where it is not supported by the syntax.

OK:

CAST(<expr> AS SIGNED)
CAST(<expr> AS SIGNED INTEGER)
CAST(<expr> AS UNSIGNED)
CAST(<expr> AS UNSIGNED INTEGER)

WRONG:

CAST(<expr> AS INTEGER)

See documentation for CAST() and CONVERT() to read about the supported data type syntax: https://dev.mysql.com/doc/refman/8.0/en/cast-functions.html

Advertisement

Answer