Hi, I want ti change fields order in the table Screenshot by Lightshot (second_field before first_field). Internet says that I can do this only by SQL request such as ALTER TABLE “tb1” ADD COLUMN “second_field” VARCHAR(100) BEFORE “first_field”;
but I receive the mistake "Statement does not generate a result set "
And I don’t want to create new table or somethink else.
Thank you)
why do you want change fields order. You can to fill table uses a form. Field order doesn’t matter in any case
I know that it doesn’t matter in forms, reports, requests, but I simple want to know if there is some possibility to make flelds to order that I want)
Please specify which database you are using as this makes a difference in the answer.
LibreOffice Base, I have a table “tb1” and want change order of fields - second_field than first_field Screenshot by Lightshot
Hello @skif,
I don’t believe you understood the question in the comment. In Base you can connect to different databases. The default is HSQLDB embedded & the default with Experimental features turned on is Firebird embedded. You can also connect to many other external databases such as PostgreSQL, MariaDB, MySQL, etc. SQL used in any situation is dependent upon the database being used & not upon Base itself.
Now as with most SQL other than SELECT statements, as yours is an ALTER statement, it must be run from the menu Tools->SQL... and NOT from the Query section of Base.
As for moving the order of fields, if this were Firebird, you could use a statement such as:
Syntax: ALTER TABLE tablename ALTER [COLUMN] colname POSITION
Example: ALTER TABLE salary_history2 ALTER old_salary POSITION 2
From Firebird documentation:
The POSITION keyword changes the position of an existing column in the notional “left-to-right” layout of the record.
Numbering of column positions starts at 1.
• If a position less than 1 is specified, an error message will be returned
• If a position number is greater than the number of columns in the table, its new position will be adjusted silently to match the number of columns.
Now if using the HSQL embedded (very old version which is currently being replaced with Firebird embedded) moving fields is a tedious process. Yes, you can insert new fields as you have stated in your SQL but there is nothing to move them as you can with some other databases.
Your method would require you to insert the new field, copy the data from the old field & then delete the old field. Time consuming, tedious, error prone and simply unnecessary since placement doesn’t matter.
Other databases may again be different.
1 Like
Thank you for clear answer! Yes, I use HSQLDB as default. Will be more detailed study the documentation. Thank you)