How to Change Column / Field order in OpenOffice Base ( HSQL )

By | May 12, 2023

Altering the sequence of fields of a table was a feature I was looking for in OpenOffice Base. It is not directly possible to drag the columns or fields and alter their sequence.

We need a sql workaround to change the order of the columns.

Lets say the sequence or order of the columns is as follows:

name | email | address | phone

Now you wish to change the order of the columns and put the 'phone' column before the address column like this:

name | email | phone | address

The above change can be accomplished using a set of sql queries as follows:

  • Add a new column named phone2 before address.
  • Copy the values of phone to phone2.
  • Drop the column phone.
  • Rename phone2 to phone

The sql code for OpenOffice Base would look something like this:

ALTER TABLE "tablename" ADD COLUMN "phone2" INTEGER BEFORE "address"
UPDATE "tablename" SET "phone2" = "phone"
ALTER TABLE "tablename" DROP COLUMN "phone"
ALTER TABLE "tablename" ALTER COLUMN "phone2" RENAME TO "phone"

Finally click View > Refresh Tables in the top menu. And open the table again for viewing.
The sequence should now be

name | email | phone | address

The above technique is the simplest generic method to alter table column order and can be applied to nearly all database engines like mysql, sqlite, mariadb, postgresql, oracle, microsoft sql etc.

About Silver Moon

A Tech Enthusiast, Blogger, Linux Fan and a Software Developer. Writes about Computer hardware, Linux and Open Source software and coding in Python, Php and Javascript. He can be reached at [email protected].

Leave a Reply

Your email address will not be published. Required fields are marked *