CODEWITHSUNDEEP
sqlite
Moti
Moti

Reputation: 927

SQLite query to delete from multiple tables

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

Upvotes: 9

Views: 20960

Answers (3)

Amir Dora.
Amir Dora.

Reputation: 2707

Method to delete all tables

 public void dropAllTables() {
            // query to obtain the names of all tables in your database
            SQLiteDatabase db = this.getWritableDatabase();
            Cursor c = db.rawQuery("SELECT name FROM sqlite_master WHERE type='table'", null);
            List<String> tables = new ArrayList<>();

            // iterate over the result set, adding every table name to a list
            while (c.moveToNext()) {
                tables.add(c.getString(0));
            }

           // call DROP TABLE on every table name
            for (String table : tables) {
                String dropQuery = "DROP TABLE IF EXISTS " + table;
                db.execSQL(dropQuery);
            }
        }

Upvotes: 0

Schwern
Schwern

Reputation: 164829

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.

Upvotes: 13

Mua Aye
Mua Aye

Reputation: 1

You need here to make a transaction query like this below :

begin transaction 
delete from fkTable where fk = @id delete from pkTable where pk = @id 
commit

Upvotes: -2

Related Questions