CODEWITHSUNDEEP
sqlpostgresqlcommon-table-expression
diplosaurus
diplosaurus

Reputation: 2588

Run multiple queries with Common Table Expression

Currently I have a query using WITH clause that I'm using to get a list of IDs from two separate tables that I plan on DELETEing.

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

The issue is that I need to use this same statement in two separate DELETE queries. One for the organizations table and one for the people table. However, if I semicolon separate these statements I lose access to ids_to_delete, and if I simply run the query again I will not get the same results.

I would like to be able to do something like this to keep access to my ids_to_delete results:

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

Additionally Organizations->People is a Many-to-one relationship, enforced by a FOREIGN KEY, so I need to delete the people first.

Using PostgreSQL 9.6.

Upvotes: 0

Views: 143

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271231

Postgres supports delete in CTEs. So you can phrase this as one query:

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

Upvotes: 4

Related Questions