Reputation: 2588
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 DELETE
ing.
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
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