如何从多个表中删除数据？ [英] How to delete data from multiple tables?

问题描述

我有这些表：

 
 事件（evt_id，evt_code，reg_id）
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 点（pt_id，evt_id）
 

我想删除所有与 evt_id = 1139 。

如何操作？

解决方案

控制您的模式，我会使模式使用级联删除。

从文章（比较相关的部分翻译为你的例子）

  CREATE TABLE point 
（
 pt_id integer PRIMARY KEY，
 evt_id integer REFERENCES event ON DELETE CASCADE 
）
  

如果您设置了级联，那么您可以从主事件表中删除，所有其他表将自动清除否则，你需要先删除所有的引用，然后删除主表。您应该在一次交易中做到这一点，以保持数据的一致性

  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; 
从幅度中删除evt_id = 1139; 
 DELETE FROM event where evt_id = 1139; 
 COMMIT; 
  

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?

解决方案

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;

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