Postgres Pro
Downloads
Home   >   mailing lists

Thread: delete where not in another table

delete where not in another table

From
"Campbell, Lance"
Date:
<div class="WordSection1"><p class="MsoNormal">If I have two tables, T1 and T2, such that both have the same primary
keyof “user_id”.<p class="MsoNormal">What is the SQL I would use to delete all rows from T1 that are not in T2?<p
class="MsoNormal"> <pclass="MsoNormal">This is one way to write the SQL but it is really inefficient:<p
class="MsoNormal"> <pclass="MsoNormal">DELETE FROM T1 WHERE T1.user_id NOT IN (SELECT user_id FROM T2 WHERE
T2.user_id=T1.user_id);<p class="MsoNormal"> <p class="MsoNormal">I was thinking there should be a way to write this
witha join.<p class="MsoNormal"> <p class="MsoNormal"> <p class="MsoNormal"> <p class="MsoNormal">Thanks,<p
class="MsoNormal"> <pclass="MsoNormal">Lance Campbell<p class="MsoNormal">Software Architect<p class="MsoNormal">Web
Servicesat Public Affairs<p class="MsoNormal">217-333-0382<p class="MsoNormal"
style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><ahref="http://illinois.edu/"><span
style="font-size:9.0pt;font-family:"Arial","sans-serif";color:blue;text-decoration:none"><imgalt="University of
Illinoisat Urbana-Champaign logo" border="0" height="33" id="Picture_x0020_1" src="cid:image003.png@01CE7CB0.2050FD30"
width="195"/></span></a><span style="font-size:9.0pt;font-family:"Arial","sans-serif";color:blue"></span><p
class="MsoNormal"> <pclass="MsoNormal"> </div>

Re: delete where not in another table

From
Igor Neyman
Date:
From: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-owner@postgresql.org] On Behalf Of Campbell, Lance
Sent: Tuesday, July 09, 2013 3:25 PM
To: pgsql-sql@postgresql.org
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);
 
Thanks,
 
Lance Campbell
Software Architect
Web Services at Public Affairs
217-333-0382

  --
Try:

DELETE  FROM t1   USING t2  WHERE t1.user_id != t2.user_id;

Test it before running on production db.

Regards,
Igor Neyman
 

Re: delete where not in another table

From
Marc Mamin
Date:
> 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