PostgreSQL：删除子查询返回的行 [英] PostgreSQL: delete rows returned by subquery

问题描述

DELETE 的基本语法是

DELETE FROM table
WHERE condition

是否有一种直接的方法在<$ c $中使用子查询/别名c> DELETE 语句，如下所示？

Is there a straightforward way to use subquery/alias in a DELETE statement, something like below?

DELETE FROM (subquery) as sub
WHERE condition

下面是一个最小的工作表，但我尝试使用subquery / alias失败：

Below is a minimal working table and my failed attempt to use subquery/alias:

---create table
create table orderT (
    id integer PRIMARY KEY,
    country_code   varchar(2),
    created_date date,
    closed_date date);

---populate table
INSERT INTO orderT VALUES (1, 'US', now(), now() + interval '1 day' * 21);
INSERT INTO orderT VALUES (2, 'CA', now(), now() + interval '1 day' * 35);

--This does not work    
    DELETE
    FROM
      (SELECT *
       FROM orderT) AS sub
    WHERE sub.id = 1;

您可以尝试使用此处。

PostgreSQL 9.5

推荐答案

否，您不能直接执行此操作。原因是可以从多个行源构造子查询，包括表，视图， VALUES 子句，其他子查询（由...构造），集合返回函数，... ...可以想象，计划者可以跟踪所有表中所有受影响的行，但是根本没有实现，太复杂了。

No, you can not do this directly. The reason is that a sub-query can be constructed from multiple row sources, including tables, views, VALUES clauses, other sub-queries (constructed of ...), set-returning functions, ... One could imagine that the planner could keep track of all affected rows in all tables, but it simply isn't implemented, too complex.

但是，您可以构造从表中删除子查询中包括的所有行的序列，如下所示：

You can, however, construct the sequence of deleting from tables all rows included in a sub-query, like so:

WITH complex_sub_query AS (
    SELECT Aid, Bid, Cid, many_more_columns
    FROM tableA
    JOIN tableB ON ...
    JOIN tableC ON ...
    ...
    WHERE complex_condition
), first_delete AS (
    DELETE FROM tableC WHERE id IN (SELECT Cid FROM complex_sub_query)
), second_delete AS (
    DELETE FROM tableB WHERE id IN (SELECT Bid FROM complex_sub_query)
)
DELETE FROM tableA WHERE id IN (SELECT Aid FROM complex_sub_query);

complex_sub_query 已实现，因此行，以下每个 DELETE 语句都可以使用，包括要删除的表的主键。由于由于外键，删除顺序通常是相关的，因此其他约束和级联删除操作必须仔细分析数据模型，才能按正确的顺序执行操作。

The complex_sub_query is materialized so the rows, including the primary keys of the tables you want to delete from, are available to each of the following DELETE statements. Since the order of deletion is often relevant due to foreign keys, other constraints and cascading deletes you have to analyze your data model carefully to do things in the right order.

这篇关于PostgreSQL：删除子查询返回的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！