Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to alter a column datatype for derby database?

I am trying to alter a datatype for a derby db column. The current price column is set as DECIMAL(5,0). I would like to alter it to DECIMAL(7,2). I did this :

alter table item alter column price set data type DECIMAL(7,2);

But it did not work, and showing the error:

Error: Only columns of type VARCHAR may have their length altered. 

May I know how is it possible to alter it? Thank you.

like image 805
jl. Avatar asked Mar 05 '10 06:03

jl.


People also ask

How do I change the datatype of a column in Postgres?

First, specify the name of the table to which the column you want to change belongs in the ALTER TABLE clause. Second, give the name of column whose data type will be changed in the ALTER COLUMN clause. Third, provide the new data type for the column after the TYPE keyword.

Can we alter column type in SQL?

The ALTER COLUMN command is used to change the data type of a column in a table.


1 Answers

Here is the Derby SQL script to change column MY_TABLE.MY_COLUMN from BLOB(255) to BLOB(2147483647):

ALTER TABLE MY_TABLE ADD COLUMN NEW_COLUMN BLOB(2147483647);
UPDATE MY_TABLE SET NEW_COLUMN=MY_COLUMN;
ALTER TABLE MY_TABLE DROP COLUMN MY_COLUMN;
RENAME COLUMN MY_TABLE.NEW_COLUMN TO MY_COLUMN;
like image 198
uı6ʎɹnɯ ꞁəıuɐp Avatar answered Sep 20 '22 15:09

uı6ʎɹnɯ ꞁəıuɐp