I am using psql (PostgreSQL) 12.3.
I have 3 tables, one being the join table.
+-----------------------------+
| supplierbookingconfirmation |
+-----------------------------+
| uri |
+-----------------------------+
+-----------------------------------------------------------+
| approvalsubmission_supplierbookingconfirmation |
+-----------------------------------------------------------+
| approvalsubmission_id | automaticallybookedservices_uri |
+-----------------------------------------------------------+
+-----------------------+
| approvalsubmission |
+-----------------------+
| id | conclusiondate |
+-----------------------+
I would like to clear legacy data older than 5 years old in all 3 tables. My problem is only one of the tables (approvalsubmission) has the date to determine the age, so I need to join the tables when I do the delete.
I have the following SQL:
DELETE from supplierbookingconfirmation sc where sc.uri IN (
SELECT distinct(c.uri) from supplierbookingconfirmation c
inner join approvalsubmission_supplierbookingconfirmation s ON c.uri = s.automaticallybookedservices_uri
inner join approvalsubmission a ON a.id = s.approvalsubmission_id
where a.conclusiondate < now() - interval '5 year');
DELETE from approvalsubmission_supplierbookingconfirmation c where c.approvalsubmission_id IN (
SELECT distinct(s.approvalsubmission_id) from approvalsubmission_supplierbookingconfirmation s
inner join approvalsubmission a ON a.id = s.approvalsubmission_id
where a.conclusiondate < now() - interval '5 year');
DELETE from approvalsubmission a where a.conclusiondate < now() - interval '5 year';
However, when I try delete from the from the first table (supplierbookingconfirmation), I get the following error:
ERROR: update or delete on table "supplierbookingconfirmation" violates foreign key constraint "fk8de3b77230a9f24d" on table "approvalsubmission_supplierbookingconfirmation" DETAIL: Key (uri)=(31fb11ff-2acd-4776-b211-e2bef5daac2d) is still referenced from table "approvalsubmission_supplierbookingconfirmation". SQL state: 23503
If I try delete from the join table (approvalsubmission_supplierbookingconfirmation) first, then the delete from the supplierbookingconfirmation table won't work because the other query needs to use the join table.
Question
What is my best approach? Do I need store the results, and then delete the join table followed by the supplierbookingconfirmation table?
You could use CTEs:
WITH rem AS (
DELETE FROM approvalsubmission_supplierbookingconfirmation
WHERE ...
RETURNING approvalsubmission_id, automaticallybookedservices_uri
), del1 AS (
DELETE FROM supplierbookingconfirmation AS s
USING rem
WHERE s.uri = rem.automaticallybookedservices_uri
)
DELETE FROM approvalsubmission AS a
USING rem
WHERE a.id = rem.approvalsubmission_id;
That way you can delete from all tables in a single statement, and the delete from approvalsubmission_supplierbookingconfirmation happens first.