Page 1 of 1

[Solved] Delete all records from a table

PostPosted: Wed Sep 05, 2018 1:06 pm
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.

Re: Delete all records from a table

PostPosted: Wed Sep 05, 2018 1:30 pm
by Villeroy
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.

Re: Delete all records from a table

PostPosted: Wed Sep 05, 2018 5:30 pm
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.

Re: Delete all records from a table

PostPosted: Wed Sep 05, 2018 6:06 pm
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

Re: Delete all records from a table

PostPosted: Thu Sep 06, 2018 9:15 am
by Maax555
Many thanks, I do understand that the primary key number is meaningless. Your help appreciated.