Postgres Pro
Downloads
Home   >   mailing lists

Re: Finding rows in table T1 that DO NOT MATCH any row in table T2 - Mailing list pgsql-performance

From Shaul Dar
Subject Re: Finding rows in table T1 that DO NOT MATCH any row in table T2
Date
Msg-id 234efe30910200546rde552d7nc460ad4187d26a@mail.gmail.com
Whole thread Raw
In response to Finding rows in table T1 that DO NOT MATCH any row in table T2  (Shaul Dar <shauldar@gmail.com>)
List pgsql-performance
Tree view
How about:

DELETE * FROM T1 LEFT JOIN T2 ON T1.PK = T2.FK
WHERE T2.FK IS NULL

Shaul


On Tue, Oct 20, 2009 at 2:37 PM, Shaul Dar <shauldar@gmail.com> wrote:
Hi,

I have two large tables T1 and T2, such that T2 has a FK to T1 (i.e. T2.FK --> T1.PK, possibly multiple T2 rows may reference the same T1 row). I have deleted about 2/3 of table T2. I now want to delete all rows in T1 that are not referenced by T2, i.e. all rows in T1 that cannot join with (any row in) T2 on the condition T2.FK = T1.PK (the opposite of a join...)

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)

What is an efficient way to do this?
Thanks,

-- Shaul


pgsql-performance by date:

Previous
From: Shaul Dar
Date:
Subject: Finding rows in table T1 that DO NOT MATCH any row in table T2
Next
From: "A. Kretschmer"
Date:
Subject: Re: Finding rows in table T1 that DO NOT MATCH any row in table T2