Re: [PERFORM] Finding rows in table T1 that DO NOT MATCH any row in table T2

2009-10-21 Thread Scott Carey
On 10/21/09 4:52 AM, "Shaul Dar" wrote: > Tom, > > 1. Actually I just tested you suggestion > > SELECT COUNT (*) FROM T1 where NOT EXISTS > (SELECT 1 FROM T2 where T1.PK = T2.FK ) > > and in worked in PG 8.3.8. On a DB with 6M T1 records and 5M T2 records it >

Re: [PERFORM] Finding rows in table T1 that DO NOT MATCH any row in table T2

2009-10-21 Thread Shaul Dar
Tom, 1. Actually I just tested you suggestion SELECT COUNT (*) FROM T1 where NOT EXISTS (SELECT 1 FROM T2 where T1.PK = T2.FK ) and in worked in PG 8.3.8. On a DB with 6M T1 records and 5M T2 records it took 1m8s, My suggestion, i.e. SELECT COUNT(*) FROM T1 LEFT

Re: [PERFORM] Finding rows in table T1 that DO NOT MATCH any row in table T2

2009-10-20 Thread Melton Low
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 wrote: > Shaul Dar 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 wh

Re: [PERFORM] Finding rows in table T1 that DO NOT MATCH any row in table T2

2009-10-20 Thread Tom Lane
Shaul Dar 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 s

Re: [PERFORM] Finding rows in table T1 that DO NOT MATCH any row in table T2

2009-10-20 Thread A. Kretschmer
In response to Shaul Dar : > 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

Re: [PERFORM] Finding rows in table T1 that DO NOT MATCH any row in table T2

2009-10-20 Thread Shaul Dar
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 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 r