Results 1 to 8 of 8

Thread: RENAME column

  1. #1
    Join Date
    Jul 2004
    Location
    Ottawa, Canada
    Posts
    58

    Unanswered: RENAME column

    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.

  2. #2
    Join Date
    Apr 2004
    Location
    Inside Intel
    Posts
    165

    Talking

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

    Newbie

  3. #3
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    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

  4. #4
    Join Date
    Jul 2004
    Location
    Ottawa, Canada
    Posts
    58
    cool, thanks

  5. #5
    Join Date
    Jan 2003
    Posts
    1,605
    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

  6. #6
    Join Date
    Jan 2004
    Location
    Europe, Finland, Helsinki
    Posts
    64
    "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

  7. #7
    Join Date
    Nov 2002
    Location
    Switzerland
    Posts
    524
    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 ;
    F. Celaia
    DBA Sybase/DB2/Oracle/MS-SQL

  8. #8
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    There currently is no ALTER TABLE ... DROP COLUMN in DB2.

    Andy

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •