Kbase 20343: How to modify the definition of a table and keep the existing data without using the ALTER TABLE sta
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
4/8/2005 |
|
Status: Verified
GOAL:
How to modify the definition of a table and keep the existing data without using the ALTER TABLE statement.
FACT(s) (Environment):
Progress 9.0x
Progress 9.1x
FIX:
Use a "temporary table" that has the anticipated new structure:
In this example, the initial table is called "tableA" and is defined with:
CREATE TABLE tableA
(
pkint INTEGER NOT NULL,
mymessage VARCHAR(40) DEFAULT '1234567890',
CONSTRAINT pkmytable PRIMARY KEY (pkint)
);
1) Create the "temporary table" with the statement:
CREATE TABLE modified_tableA
(
pkint , mymessage , mylabel VARCHAR(4) DEFAULT 'toto'
)
AS SELECT pkint , mymessage , 'toto' FROM tableA ;
COMMIT;
2) Drop the table you want to alter:
DROP TABLE tableA;
COMMIT;
3) Recreate TableA with it's new structure:
CREATE TABLE tableA
(
pkint , mymessage , mylabel,
CONSTRAINT pkmytable PRIMARY KEY (pkint)
)
AS SELECT pkint , mymessage , mylabel FROM modified_tableA ;
COMMIT;
3) Drop the intermediate table:
DROP TABLE modified_tableA;
COMMIT;