Re: dropping a column

Ceri Moran wrote:
>
> Hi,
>
> I know this may seem a stupid question, but I am using Oracle 7.3 for
> NT, and I am having dificulty altering the table.
>
> I have two columns in the middle of a table, I wish to move to the
> end, I cant seem to reorder using the Navigator or SQLPLUS.
>
> I have tried removing and adding them in, but cant seem to find the
> correct SQL to allow me to do this.
>
> Please could you advise me the correct SQL for dropping a non-key
> column from a table?
>
> Thanks
>
> Ceri Moran

No single SQL statement will do it. It's takes a combination of several SQL statements. You have to create a new table from the old table, drop the old table, rename the new table to the old table, and recreate any indexes that dropped as part of the table drop. Example:

SQL> create table reorder

  2      (col1 varchar2(1), 
  3       col2 varchar2(1), 
  4       col3 varchar2(1));
 

Table created.  

SQL> desc reorder

 Name                            Null?    Type
 ------------------------------- -------- ----
 COL1                                     VARCHAR2(1)
 COL2                                     VARCHAR2(1)
 COL3                                     VARCHAR2(1)
 

SQL> insert into reorder values('1','2','3');  

1 row created.  

SQL> select * from reorder;  

C C C
- - -
1 2 3  

SQL> create table reorder_temp
  2 as select col3, col2, col1 from reorder;  

Table created.  

SQL> desc reorder_temp

 Name                            Null?    Type
 ------------------------------- -------- ----
 COL3                                     VARCHAR2(1)
 COL2                                     VARCHAR2(1)
 COL1                                     VARCHAR2(1)
 

SQL> select * from reorder_temp;  

C C C
- - -
3 2 1  

SQL> drop table reorder;  

Table dropped.  

SQL> rename reorder_temp to reorder;  

Table renamed.  

SQL> select * from reorder;  

C C C
- - -
3 2 1  

SQL> desc reorder

 Name                            Null?    Type
 ------------------------------- -------- ----
 COL3                                     VARCHAR2(1)
 COL2                                     VARCHAR2(1)
 COL1                                     VARCHAR2(1)
 

SQL> mailto:brian.maclean_at_teldta.com Received on Tue Jul 08 1997 - 00:00:00 CDT