How to delete from multiple unrelated tables in single statement in SQLite?-CodePudding

I need to implement a function that simply purges the database of all data except for a few tables. Given how the code is written so far, it would be easier to do that than to implement an outside function that actually deletes the entire database file and creates a new one.

I want to do this on one prepared statement, so that I can avoid code bloat by having multiple statements and executing them in sequence. I would also like to do it in a single transaction using begin and commit just in case. What I tried is this:

begin;
DELETE FROM dataTable;
DELETE FROM cacheTable;
DELETE FROM someOtherTable;
commit;

Running this in DBeaver, not only it does not work, it actually somehow leaves the database with the transaction still open, judging from this error that I get on a second try:

SQL Error [1]: [SQLITE_ERROR] SQL error or missing database (cannot start a transaction within a transaction)

I believe what is happening is only the first line is executed and everything after a semicolon is ignored.

Can this be done in one statement?

CodePudding user response：

I want to do this on one prepared statement, so that I can avoid code bloat by having multiple statements and executing them in sequence.

Each has to be a single statement (unless ....).

However if you wanted to do via a single statement then although a bit convoluted you could use a TRIGGER which will can multiple statements (limitations apply) within a single transaction.

I would also like to do it in a single transaction using begin and commit just in case.

If you use a single statement then it is always a single transaction and there is no need for BEGIN .... COMMIT.

If you take the TRIGGER route then here's an example/demo.

The TRIGGER would need to be triggered and thus you could utilise a table specifically for this (utilising other tables could lead to inadvertent triggering).
The example/demo below uses such a table namely triggerTable.
- It will typically be empty (cleared along with the other tables by the Trigger).
- As the TRIGGER is an AFTER INSERT trigger (as per the demo), inserting a row into the triggerTable initiates the clearing of the other tables and also the triggerTable.
- There should be minimal overheads, very little time for the actual insert and subsequent delete of the inserted row and probably just the 1 (4k extra storage) page for the table.

Perhaps consider the following example/demo :-

/* Prepare Testing Environment */
CREATE TABLE IF NOT EXISTS dataTable (x);
CREATE TABLE IF NOT EXISTS cacheTable (y);
CREATE TABLE IF NOT EXISTS someOtherTable(z);
/* triggerTable could be another table */
CREATE TABLE IF NOT EXISTS triggerTable(id INTEGER PRIMARY KEY);
DROP TRIGGER IF EXISTS trigger_deletions;
/* The Trigger that will do the deletions in a single transaction */
CREATE TRIGGER IF NOT EXISTS trigger_deleteions AFTER INSERT ON triggerTable
 BEGIN
    DELETE FROM dataTable;
    DELETE FROM cacheTable;
    DELETE FROM someOtherTable;
    DELETE FROM triggerTable; /* if triggerTable used */
 END
;
/* Load some data */
INSERT INTO dataTable VALUES (1),(2),(3);
INSERT INTO cacheTable VALUES (1),(2),(3);
INSERT INTO someOtherTable VALUES (1),(2),(3);

/* Show the before test data PLUS trailer just in case nothing selected */
SELECT 'DT',* FROM dataTable UNION ALL SELECT 'CT',* FROM cacheTable UNION ALL SELECT 'SOT',* FROM someOtherTable UNION ALL SELECT 'trailer','trailer';

/* Initiate the Deletions */
INSERT INTO triggerTable VALUES(1);
/* Show the after test data PLUS trailer just as no data to select */
SELECT 'DT',* FROM dataTable UNION ALL SELECT 'CT',* FROM cacheTable UNION ALL SELECT 'SOT',* FROM someOtherTable UNION ALL SELECT 'trailer','trailer';
/* show the state of the trigger table */
SELECT * FROM triggerTable;

/* CleanUp Testing Environment */
DROP TABLE IF EXISTS dataTable;
DROP TABLE IF EXISTS cacheTable;
DROP TABLE IF EXISTS someOtherTable;

Running this in DBeaver, not only it does not work, it actually somehow leaves the database with the transaction still open, judging from this error that I get on a second try:

Are you executing the script as opposed to executing a statement ?

Using DBeaver using the above results in :-