How do I add a column to my existing table in a specific location?

Hi all,

I have an existing table and I want to add a column to the table, but I don't want the column to be at the end of the table I would like to place the column between 2 other columns in the table.  How do I do this?

alter table REQUEST

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Corey HabbasSecurityCommented:
Theoretically the placement of the collumn in your table shouldn't be important, as it will be with your select statement that you will be able to order your collumns as you want.

If you absolutely need to have the collumns ordered a specific way, then the only way that this can be done to my knowledge is

1) save your data to a backup or temp table
2) drop the main table
3) recreate the table with the ordering you need
4) move the data from the temp table back to the main table

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Naveen KumarProduction Manager / Application Support ManagerCommented:
but I don't want the column to be at the end of the table I would like to place the column between 2 other columns in the table.  How do I do this?   ------------> What is your requirement because i do not see any value addition by having it in the middle. It does not really matter in which order your column appears in the table.
Other option I can think of is to create a VIEW out of your table where in you can specify the column order.

So instead of selecting from the table, you do select from VIEW
donvike1Author Commented:
The SLE_DATE is not within any of my tables, this is a stand alone field that I need to enter.  I get the following error when I execute the table:  SLE_DATE: invalid identifier  
You cannot alter a table and reorder the columns like that the same way you can in mysql. byten has the corrent answer in that you need to recreate the table with the new column order.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.