Re: add a new column into the middle of an existing table

An example of Daniel's meaning

table_a contains 3 fields, fld1, fld2, fld3 and you want to add field fld2_5 to the table. And you would like fld2_5 to appear between fld2 and fld3. Adding the field is no problem, but it will be added to the end, no way around that other than to create a new table and copy all the records -- messy and not needed.

just specify the fields in your select to put them in the order you want

select fld3, fld2_5, fld1, fld2 from ...
select fld2, fld1, fld3, fld2_5 from ...
select fld1, fld2, fld2_5, fld3 from ...

"Daniel Morgan" <dmorgan_at_exesolutions.com> wrote in message news:3CCF162A.70435A5E_at_exesolutions.com...
> lei wang wrote:
>
> > Is there an easy way to add a new column into the middle of an
> > existing table? Seems to me that you have to create a new table and
> > copies data from the old table. <Alter table add column> statement
> > will only adds columns at the end of an existing table.
> >
> > I am using Oracle 8.1.7 EE on Solaris 2.7.
> >
> > Thanks.
>
> You can not ... but it is equally true that the location of a column in
> a table is absolutely irrelevant. Stick it at the end and adjust your
> thinking.
>
> Daniel Morgan
>
Received on Tue Apr 30 2002 - 18:28:54 CDT