Inserting new columns in the middle of a table?

Question

When one uses "ALTER TABLE tab ADD col", the new column gets added to the end of the table. For example:

TABLE: TAB
COL_1 COL_2 COL_4

ALTER TABLE TAB ADD COL_3

table will become

TABLE: TAB
COL_1 COL_2 COL_4 COL_3

However as the naming of my example columns suggests I'd actually like the table to end up like this:

TABLE: TAB
COL_1 COL_2 COL_3 COL_4 

With COL_3 before COL_4.

Besides rebuilding the table from scratch, is there any standard SQL that will get the job done? However if there is no standard SQL, I could still use some vendor dependent solutions for Oracle, but again a standard solution would be best.

Thanks.

Answer 1

I know it's old subject (2009) but maybe it will help someone that still looks for an answer. In MySQL, it works 2 add a column anywhere in the table.

ALTER TABLE `tablename` ADD `column_name1` TEXT NOT NULL AFTER `column_name2`;

This is 2 enter a text column, but u can set whatever properties u want for the new column, just make sure u write them with caps.

I found it with Xampp, MySQL admin, when i used it 2 insert a column in the middle of a MySQL table.

Hope it helps.

Answer 2

It works.

ALTER TABLE tablename ADD columnname datatype AFTER columnname;