RENAME column

ApoPen · #1 (**permalink**) 08-10-04, 11:15

I was wondering what the syntax is to rename an existing column in a table. I tried:

alter table userrole rename column user to username

I found that on some site, but it doesn't work.

dsusendran · #2 (**permalink**) 08-10-04, 11:22

You have to drop the table and recreate it. No other way.

Newbie

ARWinner · #3 (**permalink**) 08-10-04, 11:22

I do not know where you found it, but it is not DB2 UDB for LUW syntax.

There is no way to rename a column. You will either have to live with the old column name or:

1) save the data in the table (export)
2) drop table
3) recreate table with column new column name
4) insert the data (import)
5) recreate any objects that were dependant on the table (FK, indexes, etc)

Andy

ApoPen · #4 (**permalink**) 08-10-04, 11:25

cool, thanks

grofaty · #5 (**permalink**) 08-12-04, 05:20

Hi,

Renaming a column will be a new option in db2 8.2 LUW. But at this time you should do the ARWinner way.

Grofaty

hurmavi · #6 (**permalink**) 08-13-04, 09:26

"Rename" can sometimes be done like that:

1) Rename table (TABLE -> TABLEX)
2) Create a view (having name TABLE, selecting all columns AND renaming the problematic one.)
3) Rebind programs

However, note this
1) my knowledge is for z/OS environment
2) and RENAME has serious limitations

Cheers, Bill

fadace · #7 (**permalink**) 08-13-04, 11:05

Code:

ALTER TABLE YourTable ADD newCol ... ;
UPDATE YourTbl set NewCol=OldCol ;
ALTER TABLE YourTable DROP COLUMN OldCol ;
ALTER TABLE YourTable ADD OldCol ... ;
UPDATE YourTbl set OldCol=NewCol ;
ALTER TABLE YourTable DROP COLUMN NewCol ;

ARWinner · #8 (**permalink**) 08-13-04, 11:13

There currently is no ALTER TABLE ... DROP COLUMN in DB2.

Andy

Internet Services

Web Development

Digital Marketing

Consumer Tech