rename column with sql

[mokesql]

hi i tried to rename a column using alter table tablenem rename columnnameold columnnamenew but it gaves me the error: missing partition or subpartition keyword
what is to do? thanx for the help

kemo

 

[THoey]

I don't see any where in the 8i Complete Reference that you can rename a column. Unless someone else comes up with a better solution, I would suggest that you do a create table query to copy all of your data into a temp table, drop your table, recreate it with the new column name, and the copy all of the data back into the newly created table with the renamed column. Terry M. Hoey

 

[rcurva]

You can drop and create your column in 8i.

Alter table <TABLENAME> DROP COLUMN f1;
ALTER TABLE <TABLENAME> SET UNUSED COLUMN f1;
ALTER TABLE <TABLENAME> DROP UNUSED COLUMNS f1;

 

[THoey]

Forgot about that. You could drop it and then add it, but then you have to worry about the lost data... Terry M. Hoey

 

[SpiritOfLennon]

Erm, is it not easy to add a column, replicate the data in the new column and then drop the old one?

 

[THoey]

That would do it... Terry M. Hoey

 

[mokesql]

you all are right but that isnt realy the solution i searched for. i allready knew about the possibility of creating a new column with the name i want and then copy the date from the old column and then drop the old column but shouldn`t there be a easy way to rename a column? i found some remarks about it how to do it with alter table but that doesnt realy work. maybe i should write to oracle to tell them they should make this possible for the future releases

kemo

 

[SpiritOfLennon]

In theory you could identify all the sys tables that relate to the table data and update those entries, but you'd have to be insane to try that.