从多个表中删除的 SQLite 查询 [英] 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%';

因此，它返回一个表名列表，例如 table_1、table_2、table_3..

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

我想删除上表中 unique_col_id 等于特定值的所有行:

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;

有没有办法在一个查询中删除所有表?我的意思是集成两个查询(搜索表并使用 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...)

谢谢

推荐答案

虽然我确信您可以通过某种方式在一个语句中完成所有操作，但最好使用 交易 和/或 触发器.

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;

用触发器对此表示赞赏.这让数据库在发生某些事情时自动采取行动，例如当您从一个表中删除一列时，它可以从其他表中删除相关信息.最典型的方法是在外键上设置 ON DELETE CASCADE.

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.

这篇关于从多个表中删除的 SQLite 查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！