Re: Deleting orphan records

On Wed, 5 Feb 2003, John Smith wrote:

>
> I was referring to parent records with no children ;).
> John
> Chris Travers <chris(at)travelamericas(dot)com> wrote:Does ON DELETE CASCADE not work for you? Other than that you would have do do outer join acrobatics ;-) Best Regards;Chris Travers----- Original Message ----- From: John Smith To: pgsql-general(at)postgresql(dot)org Sent: Wednesday, February 05, 2003 12:55 PMSubject: [GENERAL] Deleting orphan records
> Does anybody have a method for deleting orphan (unreferenced) records? Is it possible to set up some sort of constraint or trigger (that performs well when lots of records are deleted ;) )?
>

I'm was sure I'd seen mention of using an extra table in the delete statement
in the docs. However, I can't see it in 7.4dev. Going by that short description
though the following might be possible:

DELETE FROM table1
WHERE
NOT EXISTS ( SELECT FROM table2 WHERE table2.forkeycol = table1.forkeycol )

According the the docs the reader is directed to the SELECT page for details of
the WHERE clause and the above would be valid for a select.

--
Nigel J. Andrews