+- 0Day Forums (https://0day.red)
+-- Forum: Coding (https://0day.red/Forum-Coding)
+--- Forum: Database (https://0day.red/Forum-Database)
+---- Forum: Oracle (https://0day.red/Forum-Oracle)
+---- Thread: Inserting new columns in the middle of a table? (/Thread-Inserting-new-columns-in-the-middle-of-a-table)
Inserting new columns in the middle of a table? - takxhkvplir - 07-31-2023
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.
RE: Inserting new columns in the middle of a table? - uplinked296309 - 07-31-2023
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).
RE: Inserting new columns in the middle of a table? - acetose375704 - 07-31-2023
<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".
RE: Inserting new columns in the middle of a table? - Drhickie1 - 07-31-2023
> ALTER TABLE TABLENAME
> ALTER COLUMN COLUMNNAME
> POSITION X;
RE: Inserting new columns in the middle of a table? - Mrdilatometer5 - 07-31-2023
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.
RE: Inserting new columns in the middle of a table? - ademption594 - 07-31-2023
It works.
ALTER TABLE tablename ADD columnname datatype AFTER columnname;
RE: Inserting new columns in the middle of a table? - adulterous111 - 07-31-2023
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".
RE: Inserting new columns in the middle of a table? - bandwagon169 - 07-31-2023
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;