Re: delete where not in another table

From: Marc Mamin <M(dot)Mamin(at)intershop(dot)de>
To: Igor Neyman <ineyman(at)perceptron(dot)com>, "Campbell, Lance" <lance(at)illinois(dot)edu>, "pgsql-sql(at)postgresql(dot)org" <pgsql-sql(at)postgresql(dot)org>
Subject: Re: delete where not in another table
Date: 2013-07-14 07:24:09
Message-ID: B6F6FD62F2624C4C9916AC0175D56D880CE095C2@jenmbs01.ad.intershop.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

> Subject: [SQL] delete where not in another table
> DELETE FROM T1 WHERE T1.user_id NOT IN (SELECT user_id FROM T2 WHERE T2.user_id=T1.user_id);

Following query use an anti join and is much faster:

delete from t1 where not exists (select user_id from t2 where t2.user_id =t1.user_id )

regards,

Marc Mamin

In response to

Browse pgsql-sql by date

  From Date Subject
Next Message Stefan Sylla 2013-07-16 02:53:25 update column based on postgis query on anther table
Previous Message Gavin Flower 2013-07-11 06:18:57 Re: DateDiff() function