Search code examples
sqlderby

alter data type of existing column from bigint to varchar in Apache derby

I am using Apache derby database v 10.9.1.0. There is one existing table Country-having column LawID of type bigint. It contains records having integer data only. Due to some business reason, I need to alter its data type from 'bigint' to 'varchar' . I tried following two ways to alter existing table. But both ways did not work.

a. first way 

    ALTER TABLE Country ADD COLUMN LawID_NEW VARCHAR(50);
    UPDATE Country SET LawID_NEW  = LawID;
    ALTER TABLE Country DROP COLUMN LawID;
    RENAME COLUMN Country.LawID_NEW TO LawID;

It shows message like :Columns of type 'VARCHAR' cannot hold values of type 'BIGINT'.

b. second way

ALTER TABLE Country ALTER LawID SET DATA TYPE VARCHAR(50);

It shows error message like : Invalid type specified for column 'LawID'. The type of a column may not be changed.

Any help related to correct alter query is highly appreciated, Thanks

Solution

  • I think the first method would work with this change:

    UPDATE Country SET LawID_NEW = TRIM(CHAR(LawID));