Re: DELETE WHERE EXISTS unexpected results - Mailing list pgsql-sql
| From | Jeff Bland |
|---|---|
| Subject | Re: DELETE WHERE EXISTS unexpected results |
| Date | |
| Msg-id | OF6352A213.59D91085-ON852577EC.0052E5C4-852577EC.00530F52@us.ibm.com Whole thread Raw |
| In response to | Re: DELETE WHERE EXISTS unexpected results (Carla <cgourofino@hotmail.com>) |
| List | pgsql-sql |
<br /><font face="sans-serif" size="2">The second example you gave worked for me. Thanks Carla ! </font><br /><font face="sans-serif"size="2"><br /> D. Jeff Bland<br /> z/OS System House Installation and Packaging (zSHIP)<br /> BLAND atIBMUS<br /> bland@us.ibm.com<br /></font><a href="http://w3.pok.ibm.com/zos/i95a/"><font face="sans-serif" size="2">http://w3.pok.ibm.com/zos/i95a/</font></a><fontface="sans-serif" size="2"><br /> 845-435-4210 8/295-4210<br />Famous quote: Beauty is in the eye of the beer holder.</font><br /><br /><br /><table width="100%"><tr valign="top"><td><fontcolor="#5f5f5f" face="sans-serif" size="1">From:</font></td><td><font face="sans-serif" size="1">Carla<cgourofino@hotmail.com></font></td></tr><tr valign="top"><td><font color="#5f5f5f" face="sans-serif"size="1">To:</font></td><td><font face="sans-serif" size="1">Jeff Bland/Poughkeepsie/IBM@IBMUS</font></td></tr><tr><tdvalign="top"><font color="#5f5f5f" face="sans-serif" size="1">Cc:</font></td><td><fontface="sans-serif" size="1">pgsql-sql@postgresql.org</font></td></tr><tr valign="top"><td><fontcolor="#5f5f5f" face="sans-serif" size="1">Date:</font></td><td><font face="sans-serif" size="1">12/01/201008:05 AM</font></td></tr><tr valign="top"><td><font color="#5f5f5f" face="sans-serif" size="1">Subject:</font></td><td><fontface="sans-serif" size="1">Re: [SQL] DELETE WHERE EXISTS unexpected results</font></td></tr><trvalign="top"><td><font color="#5f5f5f" face="sans-serif" size="1">Sent by:</font></td><td><fontface="sans-serif" size="1">cgourofino@gmail.com</font></td></tr></table><br /><hr noshade /><br /><br/><br /><font face="sans-serif" size="2">You don't have to include the name of the "delete table" in the subselect.<br/><br /> Wrong:<br /><br /> DELETE FROM SP.TST_USER_TBL WHERE EXISTS</font><font size="3"> </font><font face="sans-serif"size="2"><br /> (SELECT </font><a href="http://sp.tst_user_tbl.name/" target="_blank"><font color="blue"face="sans-serif" size="2"><u>SP.TST_USER_TBL.NAME</u></font></a><font face="sans-serif" size="2"> FROM <b>SP.TST_USER_TBL,</b>SP.TST_OWNER_TBL WHERE TYPE='BLAND' AND PLACE='HOME' AND </font><a href="http://sp.tst_owner_tbl.name/"target="_blank"><font color="blue" face="sans-serif" size="2"><u>SP.TST_OWNER_TBL.NAME</u></font></a><fontface="sans-serif" size="2">=</font><a href="http://sp.tst_user_tbl.name/"target="_blank"><font color="blue" face="sans-serif" size="2"><u>SP.TST_USER_TBL.NAME</u></font></a><fontface="sans-serif" size="2">) </font><font size="3"><br /><br /> Right:<br/></font><font face="sans-serif" size="2"><br /> DELETE FROM SP.TST_USER_TBL WHERE EXISTS</font><font size="3"></font><font face="sans-serif" size="2"><br /> (SELECT </font><a href="http://sp.tst_user_tbl.name/" target="_blank"><fontcolor="blue" face="sans-serif" size="2"><u>SP.TST_USER_TBL.NAME</u></font></a><font face="sans-serif"size="2"> FROM </font><a href="http://sp.tst_owner_tbl.name/" target="_blank"><font color="blue" face="sans-serif"size="2"><u>SP.TST_OWNER_TBL</u></font></a><font face="sans-serif" size="2"> WHERE TYPE='BLAND' AND PLACE='HOME' AND </font><a href="http://sp.tst_owner_tbl.name/" target="_blank"><font color="blue" face="sans-serif" size="2"><u>SP.TST_OWNER_TBL.NAME</u></font></a><fontface="sans-serif" size="2">=</font><a href="http://sp.tst_user_tbl.name/"target="_blank"><font color="blue" face="sans-serif" size="2"><u>SP.TST_USER_TBL.NAME</u></font></a><fontface="sans-serif" size="2">) </font><font size="3"><br /><br /> Or:<br/><br /> DELETE FROM </font><a href="http://sp.tst_user_tbl.name/" target="_blank"><font color="blue" face="sans-serif"size="2"><u>SP.TST_USER_TBL</u></font></a><font size="3"> WHERE PLACE = 'HOME' AND NAME IN (SELECT NAMEFROM </font><a href="http://sp.tst_owner_tbl.name/" target="_blank"><font color="blue" face="sans-serif" size="2"><u>SP.TST_OWNER_TBL</u></font></a><fontsize="3"> WHERE TYPE = 'BLAND');<br /><br /><br /> Carla O.<br /></font><br/><font size="3">2010/11/30 Jeff Bland <</font><a href="mailto:bland@us.ibm.com" target="_blank"><font color="blue"size="3"><u>bland@us.ibm.com</u></font></a><font size="3">></font><br /><font face="sans-serif" size="2"><br/> I want to delete certain rows from table USER_TBL. </font><font size="3"> </font><font face="sans-serif"size="2"><br /> Two tables are involved. USER_TBL and OWNER_TBL. </font><font size="3"><br /></font><fontface="sans-serif" size="2"><br /> The entries that match BLAND type in OWNER table and who also have a matchingentry in USER table NAME but only for USER_TBL entries with places equal to HOME. </font><font size="3"><br /></font><fontface="sans-serif" size="2"><br /> DELETE FROM SP.TST_USER_TBL WHERE EXISTS</font><font size="3"> </font><fontface="sans-serif" size="2"><br /> (SELECT </font><a href="http://sp.tst_user_tbl.name/" target="_blank"><fontcolor="blue" face="sans-serif" size="2"><u>SP.TST_USER_TBL.NAME</u></font></a><font face="sans-serif"size="2"> FROM SP.TST_USER_TBL, SP.TST_OWNER_TBL WHERE TYPE='BLAND' AND PLACE='HOME' AND </font><a href="http://sp.tst_owner_tbl.name/"target="_blank"><font color="blue" face="sans-serif" size="2"><u>SP.TST_OWNER_TBL.NAME</u></font></a><fontface="sans-serif" size="2">=</font><a href="http://sp.tst_user_tbl.name/"target="_blank"><font color="blue" face="sans-serif" size="2"><u>SP.TST_USER_TBL.NAME</u></font></a><fontface="sans-serif" size="2">) </font><font size="3"><br /><br /></font><fontface="sans-serif" size="2"><br /> Example :</font><font size="3"><br /></font><font face="sans-serif" size="2"><br/> <b>OWNER_TBL</b> <b>USER_TBL</b></font><font size="3"><br /></font><fontface="sans-serif" size="2"><br /> <u> NAME TYPE PLACE NAME</u></font><fontsize="3"> </font><font face="sans-serif" size="2"><br /> BLAND BLAND WORK BLAND</font><font size="3"> </font><font face="sans-serif" size="2"><br /> LARRY BLAND HOME BLAND</font><font size="3"> </font><font face="sans-serif" size="2"><br/> MOE BLAND HOME LARRY</font><font size="3"> </font><fontface="sans-serif" size="2"><br /> CURLY BLAND WORK LARRY</font><fontsize="3"> </font><font face="sans-serif" size="2"><br /> JOE BLAND HOME MOE</font><font size="3"><br /><br /></font><font face="sans-serif" size="2"><br /> In the end I expectthe USER_TBL to <b>not </b>contain the 3 HOME entries. <br /> But what is happening is the whole USER_TBL is emptyafter the query. </font><font size="3"><br /></font><font face="sans-serif" size="2"><br /> Any ideas or tips.. Thanks.</font><br/><br /><br />