[Solved] Delete all records from a table

Creating tables and queries
Post Reply
Maax555
Posts: 127
Joined: Tue Mar 28, 2017 11:56 am

[Solved] Delete all records from a table

Post by Maax555 »

Hi, as I build and test my database I am adding new records to test various forms and functions are working. I now find I have a lot of records in my main table which have random data in them. Would it be the correct thing to do to copy and paste this table using the definition only option? Then delete old table and rename newly copied one?

many thanks.
Last edited by Maax555 on Thu Sep 06, 2018 9:15 am, edited 1 time in total.
Regards Maax
LibreOffice 6.1.4.2 on Windows 7
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Delete all records from a table

Post by Villeroy »

menu:Tools>SQL...

Code: Select all

DELETE FROM "Table Name"
menu:View>Refresh Tables (refresh the Base GUI)
You can append a WHERE clause to delete only records matching some criteria.

In the Base GUI you can delete selected records (Ctrl+click row headers), filtered records, records returned from a query if the primary key is included in the query.
You can select all records by clicking the empty grey box in the corner between row headers and column headers. Then right-click>Delete.

If you get a consistency error, then some of your records are referenced by other tables and you have to think about how to handle this situation.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Maax555
Posts: 127
Joined: Tue Mar 28, 2017 11:56 am

Re: Delete all records from a table

Post by Maax555 »

Ok, my apologies as I was aware you could right click and delete. I neglected to mention that i would like to have the records return to number 1 also instead of carrying on from the last deleted record. I know the user will not see this but if its possible and makes sense I would like to do this?

thanks again.
Regards Maax
LibreOffice 6.1.4.2 on Windows 7
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Delete all records from a table

Post by Villeroy »

Auto-incrementing primary keys are meaningless. They serve technical purposes only. It should not matter if they start with 1 or anything else. Every one of these values may be used as a reference in some other table, so it makes no sense to change them across the whole database. If you want to restart the key anyway, refer to the HSQL documentation http://www.hsqldb.org/doc/1.8/guide/ch0 ... le-section
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Maax555
Posts: 127
Joined: Tue Mar 28, 2017 11:56 am

Re: Delete all records from a table

Post by Maax555 »

Many thanks, I do understand that the primary key number is meaningless. Your help appreciated.
Regards Maax
LibreOffice 6.1.4.2 on Windows 7
Post Reply