Postgresql从多个表中删除多行

Question

考虑2个或多个表格：

users (id, firstname, lastname)
orders (orderid, userid, orderdate, total)

我希望删除所有名字为Sam的用户以及他们的订单。在mysql中，我通常使用左连接。在此示例中，userid对我们来说是未知的。

查询的正确格式是什么？

- fawzib

Answer 1

安排适当的级联删除是明智的，通常是解决这个问题的正确方法。 对于某些特殊情况，还有另一种相关的解决方案。

如果您需要基于公共数据集执行多个删除操作，则可以使用公共表达式（CTE）。

很难举一个简单的例子，因为主要用例可以通过级联删除来处理。

例如，我们要删除表A中所有值在表B中正在删除的值集合中的项目。 通常这些将是键，但如果它们不是，则无法使用级联删除。

要解决此问题，您可以使用CTE。

WITH Bdeletes AS (
    DELETE from B where IsSomethingToDelete = true returning ValueThatRelatesToA
)
delete from A where RelatedValue in (select ValueThatRelatesToA from Bdeletes)

这个例子故意保持简单，因为我的重点不是争论按键映射等问题，而是展示如何在共享数据集上执行两个或多个删除操作。这也可以更加复杂，包括更新命令等。

这是一个更复杂的例子（来自达斯维达的个人数据库）。在这种情况下，我们有一个引用地址表的表格。如果地址在他摧毁的星球清单中，我们需要从地址表中删除它们。我们希望使用这些信息从人员表中进行删除，但只有当他们在星球上（或在他的战利品列表中）时才进行。

with AddressesToDelete as (
    select AddressId from Addresses a 
    join PlanetsDestroyed pd on pd.PlanetName = a.PlanetName
),
PeopleDeleted as (
    delete from People 
    where AddressId in (select * from AddressesToDelete)
    and OffPlanet = false 
    and TrophyKill = false
    returning Id
),
PeopleMissed as (
    update People 
    set AddressId=null, dead=(OffPlanet=false)
    where AddressId in (select * from AddressesToDelete)
    returning id
)
Delete from Addresses where AddressId in (select * from AddressesToDelete)

现在他的数据库已经更新。地址删除不会导致完整性故障。请注意，虽然我们正在从更新和第一次删除返回数据，但这并不意味着我们必须使用它。我不确定是否可以在没有返回数据的情况下在CTE中放置一个删除（我的SQL语句可能也有误 - 我无法测试运行此语句，因为Darth V.心情不佳）。

- Chaos Crafter

---

我觉得你缺乏信仰，如果你不需要RETURNING子句，RETURNING NULL将是一个可以接受的替代品...或者你可以完全省略它。提示：应该可以这样写“DELETE FROM people USING AddressesToDelete WHERE AddressId = AddressesToDelete.id AND OffPlanet = false AND TrophyKill = false”。 - Klaws

查询 PeopleDeleted 没有运行，对吗？ - Erik van Velzen

Answer 2

http://www.postgresql.org/docs/current/static/sql-delete.html

DELETE 
FROM orders o
USING users u
WHERE o.userid = u.id
  and u.firstname = 'Sam';

DELETE 
FROM users u
WHERE u.firstname = 'Sam';

您还可以使用 ON delete cascade 创建表格。

http://www.postgresql.org/docs/current/static/ddl-constraints.html

CREATE TABLE order_items (
    product_no integer REFERENCES products ON DELETE RESTRICT,
    order_id integer REFERENCES orders ON DELETE CASCADE,
    quantity integer,
    PRIMARY KEY (product_no, order_id)
);

- Juan Carlos Oropeza

Answer 3

将userid定义为外键，指向users（id）并启用级联删除，例如：

create table users (
    id int primary key, 
    firstname text, 
    lastname text);

create table orders (
    orderid int primary key, 
    userid int references users (id) on delete cascade, 
    orderdate date, 
    total numeric);

delete from users
where firstname = 'Sam';

- klin

---

非常感谢！这对未来会很有帮助。 - fawzib