[Solved] Delete all records from a table

Creating tables and queries

[Solved] Delete all records from a table

Postby Maax555 » Wed Sep 05, 2018 1:06 pm

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
Maax555
 
Posts: 127
Joined: Tue Mar 28, 2017 11:56 am

Re: Delete all records from a table

Postby Villeroy » Wed Sep 05, 2018 1:30 pm

menu:Tools>SQL...
Code: Select all   Expand viewCollapse view
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, OpenOffice 4.x & LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 26627
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Delete all records from a table

Postby Maax555 » Wed Sep 05, 2018 5:30 pm

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
Maax555
 
Posts: 127
Joined: Tue Mar 28, 2017 11:56 am

Re: Delete all records from a table

Postby Villeroy » Wed Sep 05, 2018 6:06 pm

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, OpenOffice 4.x & LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 26627
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Delete all records from a table

Postby Maax555 » Thu Sep 06, 2018 9:15 am

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
Maax555
 
Posts: 127
Joined: Tue Mar 28, 2017 11:56 am


Return to Tables & Queries

Who is online

Users browsing this forum: No registered users and 3 guests