How to move columns in java DB Apache Derby?

Question

I need to change specific column's position I tried Ted Hopp's solution in Move Column in MYSQL

ALTER TABLE EMPLOYEES MODIFY COLUMN fname VARCHAR(25) AFTER password

and i got this error:

Error code -1, SQL state 42X01: Syntax error: Encountered "MODIFY" at line 1, column 22.

So any idea how to do it with Derby

Why do you need to move the column? You can always re-arrange the columns in the order you prefer in your SELECT statement, or in your program after you retrieve the data. — Bryan Pendleton
– Bryan Pendleton, Commented Nov 18, 2013 at 14:51
i have a db and i did some changes, so i wanted to sort columns because i knew it is possible in mysql. and i was wondering if derby have the same feature or not. :) — user2954718
– user2954718, Commented Nov 18, 2013 at 14:59
If it's really important to you, you could: (a) create a new table, with the columns in the order you want, (b) insert into newtable select column-list from oldtable (c) drop table oldtable, (d) rename table newtable to oldtable. — Bryan Pendleton
– Bryan Pendleton, Commented Nov 19, 2013 at 4:47

Aaron Digulla · Accepted Answer · 2013-11-18 13:08:02Z

4

Derby uses ADD COLUMN (docs):

ALTER TABLE EMPLOYEES ADD COLUMN fname VARCHAR(25)

There is no way to insert columns before or after a certain column; they are always appended.

answered Nov 18, 2013 at 13:08

Aaron Digulla

330k111 gold badges626 silver badges840 bronze badges

Sign up to request clarification or add additional context in comments.

1 Answer 1