SQLite query to delete from multiple tables

Question

I use a query to retrieve a list of tables with a specific column name:

select name from sqlite_master where type='table' and sql like '%unique_col_id%';

So, it return a list of table names for example, table_1, table_2, table_3..

I would like to delete all rows in the above tables with unique_col_id equal to specific value:

DELETE FROM table_1 where unique_col_id=3;
DELETE FROM table_2 where unique_col_id=3;
DELETE FROM table_3 where unique_col_id=3;

Is there a way to delete all table in one query? I mean to integrate both queries (search the table and delete all of them with unique_col_id=3...)

Thanks

Answer 1

While I'm sure there is some way you can do all that in one statement, it's better to either use transactions and/or triggers.

A transaction lets you group a bunch of statements together so that nothing is saved until they all run. No other process will see your changes until the transaction is complete. If there's an error or your process dies in the middle of a transaction, all changes are thrown out. This avoids a whole host of issues. Using transactions lets you use simple statements rather than trying to smash everything together into an unmaintainable mess.

begin;
DELETE FROM table_1 where unique_col_id=3;
DELETE FROM table_2 where unique_col_id=3;
DELETE FROM table_3 where unique_col_id=3;
commit;

Compliment this with triggers. This lets the database automatically take actions when something happens, like when you delete a column from one table it can delete related information from other tables. The most typical way to do this is to set ON DELETE CASCADE on your foreign keys.

# This is necessary in SQLite else foreign keys will be ignored
sqlite> pragma foreign_keys = on;

# Make two tables with a relationship and set it ON DELETE CASCADE
sqlite> create table addresses ( id INTEGER PRIMARY KEY, address TEXT, person REFERENCES people(id) ON DELETE CASCADE );
sqlite> create table people ( id INTEGER PRIMARY KEY, name TEXT );

# Add a row with a relationship.
sqlite> insert into people (name) VALUES ("Foo Bar");
sqlite> select * from people;
1|Foo Bar
sqlite> insert into addresses (address, person) VALUES ("123 Foo St", 1);
sqlite> select * from people join addresses on addresses.person = people.id;
1|Foo Bar|1|123 Foo St|1

# Delete the parent row and the child (address) is also deleted.
sqlite> delete from people where id = 1;
sqlite> select * from people;
sqlite> select * from addresses;

This is much more robust. People making changes to your database don't need to know all the details, the database takes care of it for them.