How to alter a column datatype for derby database?

sql database derby
jl. picture jl. · Mar 5, 2010 · Viewed 34.2k times · Source

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.

Answer

uı6ʎɹnɯ ꞁəıuɐp picture uı6ʎɹnɯ ꞁəıuɐp · Nov 18, 2010

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;

Related questions

Alter a table column with auto increment by 1 in derby

I have created a table in derby Netbeans and now i realize that i need to make a column as auto incremented by 1 which is a primary key. How can i do so? I tried the following code but was …

Read More
Insert into ... values ( SELECT ... FROM ... )

I am trying to INSERT INTO a table using the input from another table. Although this is entirely feasible for many database engines, I always seem to struggle to remember the correct syntax for the SQL engine of the day (…

Read More
What is the difference between "INNER JOIN" and "OUTER JOIN"?

Also how do LEFT JOIN, RIGHT JOIN and FULL JOIN fit in?

Read More