SQL 如何从多个表中删除数据？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10145221/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to delete data from multiple tables?
提问by user1202766
I have these tables:
我有这些表：
event (evt_id, evt_code, reg_id) magnitude (mag_id, evt_id, value) trace (trace_id, pt_id) point (pt_id, evt_id)
I want to delete all rows from all tables related to evt_id=1139.
How do I do it?
我想从与evt_id=1139.
我该怎么做？
回答by Justin Pihony
If you have control over your schema, I would make the schema use cascading deletes.
如果您可以控制自己的架构，我会让架构使用级联删除。
From the article (the more pertinent portion translated for your example)
来自文章（为您的示例翻译的更相关的部分）
CREATE TABLE point
(
pt_id integer PRIMARY KEY,
evt_id integer REFERENCES event ON DELETE CASCADE
)
If you have cascades set up, then you can just delete from the main event table and all the other tables will be cleaned up automatically
如果您设置了级联，那么您只需从主事件表中删除，所有其他表将自动清理
Otherwise, you need to delete all of the references first, then you delete the main table. You should do this in one transaction to keep data consistent
否则，您需要先删除所有引用，然后删除主表。您应该在一个事务中执行此操作以保持数据一致
BEGIN;
DELETE FROM trace WHERE EXISTS
(SELECT 1 FROM point WHERE evt_id = 1139 AND trace.pt_id = point.pt_id);
DELETE FROM point where evt_id = 1139;
DELETE FROM magnitude where evt_id = 1139;
DELETE FROM event where evt_id = 1139;
COMMIT;
回答by Erwin Brandstetter
The only non-trivial element in your question are deletes from the table trace. I guess it is safe to assume trace.pt_idis referencing point.pt_id?
您问题中唯一重要的元素是从 table 中删除trace。我想可以安全地假设trace.pt_id正在引用point.pt_id?
Either you define a foreign key with ON DELETE CASCADEand just forget about the table trace(as already pointed out by @kevin) or you have to take care of depending rows manually.
要么你定义一个外键，ON DELETE CASCADE然后忘记表trace（正如@kevin已经指出的那样），要么你必须手动处理依赖行。
Since PostgreSQL 9.1you can use data-modifying CTEs:
从 PostgreSQL 9.1 开始，您可以使用数据修改 CTE：
BEGIN;
WITH x AS (
DELETE FROM point WHERE evt_id = 1139
RETURNING pt_id
)
DELETE FROM trace
USING x
WHERE trace.pt_id = x.pt_id;
DELETE FROM magnitude WHERE evt_id = 1139;
DELETE FROM event WHERE evt_id = 1139;
COMMIT;
The RETURNING clauseof the DELETE FROM pointreturns all affected pt_id- those are in turn used to delete all corresponding rows from trace.
在RETURNING子句中的DELETE FROM point回报所有受影响pt_id-这些都是交替使用，从删除所有相应的行trace。
You did not mention whether concurrencyis a problem in your case. If it is, and if you want to avoid possible results in the short time window in between deletes where rows for evt_id = 1139are present in one table while already deleted from another, wrap it all into a transaction.
If that is no concern to you, ignore BEGINand COMMIT.
您没有提到在您的情况下并发是否有问题。如果是这样，并且如果您想避免在删除之间的短时间内可能出现的结果，其中的行evt_id = 1139存在于一个表中而已从另一个表中删除，请将其全部包装到一个事务中。
如果您不关心，请忽略BEGIN和COMMIT。
To avoid deadlocksalways use the same sequence of delete statements (in all concurrent transactions). This avoids situations where one transaction would start deleting in one table, the next in the other table and then each would wait for the other.
为避免死锁，请始终使用相同的删除语句序列（在所有并发事务中）。这避免了一个事务开始在一个表中删除，下一个在另一个表中然后每个都等待另一个的情况。
回答by kgrittn
If the rows you want to delete can be envisioned as a hierarchy, you could define FOREIGN KEY constraints for the relationships with the ON DELETE CASCADE clause. Deleting the row at the top will then cascade down and delete them all.
如果可以将要删除的行设想为层次结构，则可以使用 ON DELETE CASCADE 子句为关系定义 FOREIGN KEY 约束。删除顶部的行将向下级联并将它们全部删除。
http://www.postgresql.org/docs/9.1/static/ddl-constraints.html#DDL-CONSTRAINTS-FK
http://www.postgresql.org/docs/9.1/static/ddl-constraints.html#DDL-CONSTRAINTS-FK
回答by user1327053
I assume that the id's in the different tables correspond and are used for linking. I also assume you want to delete all the trace points allthough they are only indirectly linked to evt_id
我假设不同表中的 id 对应并用于链接。我还假设您想删除所有跟踪点，尽管它们只是间接链接到 evt_id
You can delete your records from all tables as follows:
您可以按如下方式从所有表中删除您的记录：
DELETE event , magnitude, trace, point
FROM event left join magnitude on event.evt_id = magnitude.evt_id
left join point on event.evt_id = point.evt_id
left join trace on point.pt_id = trace.trace_id
where event_id=1139
回答by squarephoenix
$delete = 1139;
$query = "DELETE FROM event, magnitude, point WHERE evt_id = '$delete'";
mysql_query($query, $con);
$query = "DELETE FROM trace WHERE pt_id = '$delete'";
or just paste that statement into an .sql file and run it, or into your sql software query window, or place it in a .php file and run on the server.
或者只是将该语句粘贴到 .sql 文件中并运行它，或粘贴到 sql 软件查询窗口中，或将其放入 .php 文件中并在服务器上运行。
