How about
DELETE FROM T1 WHERE T1.PK IN
(SELECT T1.PK FROM T1 EXCEPT SELECT T2.FK FROM T2);

Mel

On Tue, Oct 20, 2009 at 7:59 AM, Tom Lane <t...@sss.pgh.pa.us> wrote:

> Shaul Dar <shaul...@gmail.com> writes:
> > I assume this will work but will take a long time:
>
> > DELETE * FROM T1 where T1.PK NOT IN
> > (SELECT T1.PK FROM T1, T2 where T1.PK = T2.FK)
>
> Well, yeah, but it's unnecessarily inefficient --- why not just
>
> DELETE FROM T1 where T1.PK NOT IN
> (SELECT T2.FK FROM T2)
>
> However, that still won't be tremendously fast unless the subselect fits
> in work_mem.  As of 8.4 this variant should be reasonable:
>
> DELETE FROM T1 where NOT EXISTS
> (SELECT 1 FROM T2 where T1.PK = T2.FK)
>
> Pre-8.4 you should resort to the "left join where is null" trick,
> but there's no need to be so obscure as of 8.4.
>
>                        regards, tom lane
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>

Reply via email to