Inverting rows (top-most to bottom-most, etc)

Discuss the database features
Post Reply
JBrownies
Posts: 1
Joined: Wed Aug 09, 2017 6:25 am

Inverting rows (top-most to bottom-most, etc)

Post by JBrownies »

Brand new here, learning databases from various YouTube tutorials mostly.
Just had a quick question; made my first table and 50 rows, 5 columns each later I realized that I had been working backwards.
ID 0 should be switched with ID 50, 1 with 49, etc.
Is there a quick, easy, painless way to do this?
OpenOffice 4.1 on Windows 10
Arineckaig
Volunteer
Posts: 828
Joined: Fri Nov 30, 2007 10:58 am
Location: Scotland

Re: Inverting rows (top-most to bottom-most, etc)

Post by Arineckaig »

Is there a quick, easy, painless way to do this?
Welcome to the forum.

It is an essential feature of a Relational Database (such as that of the RDMS embedded in Base) that the underlying data is stored in tables - aka mathematical relations - where exclusive primary key identifiers and attribute names render the ordering of records and fields immaterial.

Thus displaying or working with the stored data is better undertaken in forms that sort, or select subsets of, the records as required. Thus the most easy and painless way would be to leave your IDs unchanged but to sort your forms (and queries) as required. Unless your primary key field is required to hold pertinent or specific information, it can simply be treated as a token identity for each record.

Please come back if this reply merely confuses or you seek further help.
When this issue has been resolved, it would help other users of the forum if you add the word - [Solved] - to the Subject line of your 1st post (edit button top right).
AOOo 4.1.5 & LO 6 on MS Windows 10 MySQL and HSQLDB
User avatar
MTP
Volunteer
Posts: 1620
Joined: Mon Sep 10, 2012 7:31 pm
Location: Midwest USA

Re: Inverting rows (top-most to bottom-most, etc)

Post by MTP »

With just 50 rows you can manually edit the table: double-click on it to open the table, and type the new ID numbers in like a spreadsheet.

Be aware that at no time can your table ever have two rows with the same ID. So if you start with row 0 and try to change it to 50, it will give you an error message. You'll have to change one row to a dummy number (say, 50 to 500) and then change the other row to the correct number (0 to 50), then go back to the dummy row and change it to the correct number (500 to 0). Takes a bit of time and concentration but shouldn't be too bad.
OpenOffice 4.1.1 on Windows 10, HSQLDB 1.8 split database
Arineckaig
Volunteer
Posts: 828
Joined: Fri Nov 30, 2007 10:58 am
Location: Scotland

Re: Inverting rows (top-most to bottom-most, etc)

Post by Arineckaig »

With just 50 rows you can manually edit the table: double-click on it to open the table, and type the new ID numbers in like a spreadsheet.
Be aware also that problems will arise if the ID numbers have already been referenced from foreign key fields elsewhere in the database. I would respectfully offer a personal opinion that changing primary key ID values should be undertaken with the utmost care and only as a last resort. If in the case of a single table database with just 50 records it proves really necessary to change primary key ID values, it could well be simpler and indeed safer to delete the table and to start again.
When this issue has been resolved, it would help other users of the forum if you add the word - [Solved] - to the Subject line of your 1st post (edit button top right).
AOOo 4.1.5 & LO 6 on MS Windows 10 MySQL and HSQLDB
Post Reply