DB2 need help for alter table alter column set data type

Question

I need to change the data type of column from VARCHAR to DECIMAL

I have data in the column, but they are all numeric values like 10.00, 25.50 etc,

Now when I do alter table MY_TABLE alter column COL1 set data type to decimal(11,2) its failing.

Whats the process i can follow here.

I'm sure this is not a new question but i couldn't find the solution, so instead of raking my brains i'm asking out here.

Answer 1

The syntax is ALTER TABLE {tableName} MODIFY {Column-Name} {New-Column-Definition} I.E. ALTER TABLE MY_TABLE MODIFY COL1 DECIMAL(11,2)

Generally this (altering the column data type) won't convert the existing data to the new type and will probably set the incompatible values as NULL (or it may make a smart conversion it's pretty much DBMS server specific)

You can do something like this if the data volume is not too high

ALTER TABLE MY_TABLE ADD COLUMN Temporary decimal(11,2);
UPDATE MY_TABLE SET Temporary=CAST(COL1 AS DECIMAL(11,2));
ALTER TABLE MY_TABLE DROP COLUMN COL1;
ALTER TABLE MY_TABLE  CHANGE Temporary COL1 decimal(11,2);

Edit: The first part is probably incorrect