PostgreSQL: delete rows returned by subquery

Question

The basic syntax for DELETE is

DELETE FROM table
WHERE condition

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

DELETE FROM (subquery) as sub
WHERE condition

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;

You can try the code here.

PostgreSQL 9.5

... In my actual script, the subquery is more 90 lines long. Mine is over 300 Lines! -->> please don't hide your ignorance behind impressive numbers. — wildplasser
– wildplasser, Commented Mar 7, 2017 at 23:54

Patrick · Accepted Answer · 2017-03-08 02:26:11Z

7

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);

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.

answered Mar 8, 2017 at 2:26

Patrick

33k7 gold badges73 silver badges102 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

PirateApp Over a year ago

upvoted! what happens if you have 2 columns which are a composite primary key

Collectives™ on Stack Overflow

PostgreSQL: delete rows returned by subquery

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related