Re: Column Name Change

On Dec 2, 7:07 am, "Jeff B" <jef..._at_KnoSpam.tds.net> wrote:
> Good Morning,
>
> Is it possible to change a column name of a table after the table has been
> created? I have a table called Members that have FirstName, LastName
> columns. The problem I have is the information that got put into the table
> had the names reversed, so now all the first names are in the LastNamr
> column and all of the last names are in the FirstName columns. I tried
> Delete Members but I recieved an error about a value being shared, I found
> on one website Replace Column column_name To new_name but this did not work
> as I could not figure out how to reference which table I wanted to make that
> change in.
>
> Just kind of stuck at the moment, seems easiest thing would be just to
> cjange the column headings to match the data but I can not find a Modify or
> AlterTable command to do this?
>
> Thanks for the help,
>
> Jeff

If you are running Oracle 9i R2 and above, there is a quick way to rename a column. From a Google search on the terms: alter table rename column oracle:
http://download-uk.oracle.com/docs/cd/B10501_01/server.920/a96540/statements_32a.htm#2103992

Another option is to rename the table, then use a CREATE TABLE AS SELECT to create a new table with the old name that is based on the column definitions (rename the columns using an alias in the select), bringing along the data from the old table. You will then need to recreate any triggers that were on the old table.

Another option is to update both colunms in the table at the same time and set:
  LASTNAME=FIRSTNAME,
  FIRSTNAME=LASTNAME A fourth option is to create a view based on the table with the column names aliased to their correct meaning.

Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc. Received on Sun Dec 02 2007 - 08:04:37 CST