Обсуждение: delete where not in another table
<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>
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
> 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