Search code examples
db2alter

Error when trying to add db2 column between 2 columns


I am trying to add a new column between 2 existing columns. Here I am using "after" clause to insert the new column. Below is my query.

   alter table doc add column alt_title varchar(10) after title;

I am seeing the below error

An unexpected token "title" was found following "varchar(10) after".  
Expected tokens may include:  "REFERENCES".. SQLCODE=-104, SQLSTATE=42601, DRIVER=4.8.86
Elapsed Time:  0 hr, 0 min, 0 sec, 0 ms.

I even tried before clause,and get same results. Can someone please help me fix the issue?


Solution

  • There is no way to modify the table by adding a column in the middle, however you can recreated with the good structure.

    • You create a second table, with the same quantity of columns of the first table with the desired order.
    • You perform a insert select by providing the column names.
    • You add the constraints of the first column in the second one.
    • You drop constraints in the first table.
    • Finally, you rename tables

    You can get all this information by using db2look.