sql - 使用公用表表达式运行多个查询

Question

目前我有一个使用 WITH 子句的查询，我用它从我计划 DELETEing 的两个单独的表中获取 ID 列表。

WITH ids_to_delete AS (SELECT org.uuid, person.uuid FROM organizations org INNER JOIN people person ON org.uuid = person.org_uuid)

问题是我需要在两个单独的 DELETE 查询中使用相同的语句。一个用于 organizations 表，一个用于 people 表。但是，如果我用分号分隔这些语句，我将无法访问 ids_to_delete，如果我再次运行查询，我将不会得到相同的结果。

我希望能够做这样的事情来保持对我的 ids_to_delete 结果的访问:

WITH ids_to_delete AS (SELECT org.uuid, person.uuid FROM organizations 
org INNER JOIN people person ON org.uuid = person.org_uuid),
DELETE FROM organizations WHERE uuid IN (SELECT org.uuid FROM ids_to_delete), 
DELETE FROM people WHERE uuid IN (SELECT person.uuid FROM ids_to_delete);

此外，Organizations->People 是多对一关系，由 FOREIGN KEY 强制执行，因此我需要先删除人员。

使用 PostgreSQL 9.6。

Answer 1

Postgres 支持 CTE 中的 delete。因此，您可以将其表述为一个查询:

WITH ids_to_delete AS (
      SELECT org.uuid, person.uuid
      FROM organizations org INNER JOIN
           people person
           ON org.uuid = person.org_uuid
     ),
     o as (
      DELETE FROM organizations
      WHERE uuid IN (SELECT org.uuid FROM ids_to_delete)
      RETURNING org.uuid
     ) 
DELETE FROM people
WHERE uuid IN (SELECT person.uuid FROM ids_to_delete);