Estoy usando psql (PostgreSQL) 12.3.
Tengo 3 tablas, una de las cuales es la tabla de unión.
+-----------------------------+ | supplierbookingconfirmation | +-----------------------------+ | uri | +-----------------------------+ +-----------------------------------------------------------+ | approvalsubmission_supplierbookingconfirmation | +-----------------------------------------------------------+ | approvalsubmission_id | automaticallybookedservices_uri | +-----------------------------------------------------------+ +-----------------------+ | approvalsubmission | +-----------------------+ | id | conclusiondate | +-----------------------+ Me gustaría borrar los datos heredados de más de 5 años en las 3 tablas. Mi problema es que solo una de las tablas ( approvalsubmission , envío) tiene la fecha para determinar la antigüedad, por lo que debo unirme a las tablas cuando elimino.
Tengo el siguiente 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'; Sin embargo, cuando intento eliminar de la primera tabla ( supplierbookingconfirmation ), aparece el siguiente error:
ERROR: actualizar o eliminar en la tabla "confirmación de reserva de proveedor" viola la restricción de clave externa "fk8de3b77230a9f24d" en la tabla "aprobación de envío_confirmación de reserva de proveedor" DETALLE: la clave (uri) = (31fb11ff-2acd-4776-b211-e2bef5daac2d) todavía se referencia desde la tabla "aprobación de envío_confirmación de reserva de proveedor". Estado SQL: 23503
Si primero trato de eliminar de la tabla de unión ( approvalsubmission_supplierbookingconfirmation ), entonces la eliminación de la tabla de confirmación de reserva del supplierbookingconfirmation no funcionará porque la otra consulta necesita usar la tabla de unión.
Pregunta
¿Cuál es mi mejor enfoque? ¿Necesito almacenar los resultados y luego eliminar la tabla de combinación seguida de la tabla de confirmación de reserva del supplierbookingconfirmation ?
Podrías usar CTE:
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; De esa manera, puede eliminar de todas las tablas en una sola declaración, y la eliminación de la confirmación de la reserva del approvalsubmission_supplierbookingconfirmation se realiza primero.