13

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.

Robert Gould
  • 68,773
  • 61
  • 187
  • 272
  • 7
    Does it really matter? Column order shouldn't be relevant to anything, unless you're using `select *`, in which case, you shouldn't be.... – skaffman Nov 16 '09 at 08:36
  • @skaffman, I understand what you mean that select * is kind of rough. But it's really nice to be able to use it, especially for quick custom queries sent against the database. Until now everyone had been using select * , and my options are to fix the column oder or to reeducate everyone (and they just want to get their work done quickly and painlessly) that they need to use proper SQL. "These people" are programmers but not DB programmers and they find the need to write huge queries to answer a simple question cumbersome. – Robert Gould Nov 16 '09 at 08:44
  • @Robert Gould - Give people a text file with common queries that they can use, or at least give them "select ... FROM tablename" and they can put in the rest. – James Black Nov 16 '09 at 08:46
  • 1
    @Robert: Here an idea, then: define a view for each table, with the view having a fixed column order, and have your users `select *` from that instead of from the table. Much easier to recreate a view than a table. – skaffman Nov 16 '09 at 08:47
  • 1
    @skaffman, thanks :) Apparently that is how I'm going to solve the issue, since it doesn't seem possible to do what I originally wanted to do. – Robert Gould Nov 16 '09 at 08:50

7 Answers7

18

By default, columns are only added at the end.

To insert a column in the middle, you have to drop and recreate the table and all related objects (constraints, indices, defaults, relationships, etc).

Several tools do this for you, and depending on the size of the table, this may be an intensive operation.

You may also consider creating views on the table that display columns in the order of preferrence (overriding the actual order in the table).

Raj More
  • 47,048
  • 33
  • 131
  • 198
  • 1
    yeah the view solution was what I knew, and was going to be my first attempt, but was kind of hoping for a simpler solution :/ – Robert Gould Nov 16 '09 at 08:45
8

It works.

ALTER TABLE tablename ADD columnname datatype AFTER columnname;
user2894607
  • 165
  • 4
  • 14
3

http://www.orafaq.com/wiki/SQL_FAQ#How_does_one_add_a_column_to_the_middle_of_a_table.3F says it can't be done, and suggests workarounds of renaming the table and doing a create table as select... or (something I am unfamiliar with) "Use the DBMS_REDEFINITION package to change the structure".

ysth
  • 96,171
  • 6
  • 121
  • 214
3

ALTER TABLE TABLENAME ALTER COLUMN COLUMNNAME POSITION X;

Orhan Cinar
  • 8,403
  • 2
  • 34
  • 48
  • Is `POSITION` specific to MySQL? – Raj More Nov 16 '09 at 15:36
  • This works on MySQL too. It's not standard SQL. But the answer might help someone with a similar problem using a DB that supports this – Robert Gould Nov 18 '09 at 11:22
  • Am i doing it Wrong ? `alter table expense_testTable alter column col3 POSITION 3;` Its giving error: ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'POSITION 3' at line 1 – Dharmjeet Kumar Sep 20 '13 at 09:46
2

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.

Sherazin
  • 29
  • 1
1

If you have table col_1, col_2, col_4, you wanted to add col_3 after col_2, you can simply do this:

alter table <table_name> modify col_4 invisible;
alter table <table_name> add col_3 <type>;
alter table <table_name> modify col_4 visible;
superfive
  • 11
  • 1
  • 1
0

As per my small research the only way is to create the views of the previous table in a required order of columns or else recreate the table from the scratch and in some sql query tools the 'AFTER' keyword might work".

mouli mlsn172
  • 21
  • 1