Deleting All Records In A SqLite Table At Once

Databases
1

Hello,

I am trying to delete all the records in a SQLite Table with one click of a button.

The name of the database is Roster. The table name is Report1.

The code I am trying to use is:

[code] Roster= new RealSQLDatabase
Roster.DatabaseFile = GetFolderItem(“Roster.rsd”)

Dim sqlDelete as String

sqlDelete=“SELECT * FROM Report1”
fs=Roster.SQLSelect(sqlDelete)

Roster.SQLExecute “update Report1 set id=’’”
Roster.SQLExecute “update Report1 set Name=’’”[/code]

The code does not delete anything in the table. Can anyone see what I am doing wrong or is there an easier (or better) way to accomplish this?

Any help would be greatly appreciated.

2

sqlDelete="DELETE FROM Report1;" Roster.SQLExecute(sqlDelete)
That should do it. You need an SQLExecute instead of an SQLSelect.

You should also do some error checking to make sure all data got deleted properly before you continue :=

3

Here’s the SQLite DELETE docs for reference: http://www.sqlite.org/lang_delete.html

4

Thank you Albin and Paul. That worked great. I appreciate you taking the time to help me.

Thanks again. :slight_smile:

5

Just a side note… while this is the way to do it with SQLite… it may not be the best way for other Database Engines.

If the database engine supports “TRUNCATE TABLE xxxx” use it instead (SQLite does not have this syntax).

For example in Oracle… saying “DELETE FROM xxxx” causes a UNDO pool to be created until a COMMIT is executed.
Where a “TRUNCATE xxxx” does not.

6

[quote=33483:@Dave S]Just a side note… while this is the way to do it with SQLite… it may not be the best way for other Database Engines.

If the database engine supports “TRUNCATE TABLE xxxx” use it instead (SQLite does not have this syntax).

For example in Oracle… saying “DELETE FROM xxxx” causes a UNDO pool to be created until a COMMIT is executed.
Where a “TRUNCATE xxxx” does not.[/quote]

That really depends on whether you want to be able to rollback the delete or not
If not truncate in a lot of db engines is way faster

7

right… but once you commit… rollback is not possible… and the truncate is faster because the rollback cache is not created.

8

Sure
And if all you really want is to immediately & irrevocably remove the contents without the possibility of rolling it back a truncate is usually the quickest

But that’s not what I said

If you do a “delete from …” operation in a transaction you have the possibility of rolling it back
If you truncate you usually don’t regardless of whether its in a transaction or not

So, as I said [quote=33522:@Norman Palardy]That really depends on whether you want to be able to rollback the delete or not
If not truncate in a lot of db engines is way faster[/quote]