Thread: DELETE WHERE EXISTS unexpected results
<br /><font face="sans-serif" size="2">I want to delete certain rows from table USER_TBL. </font><br /><font face="sans-serif"size="2">Two tables are involved. USER_TBL and OWNER_TBL. </font><br /><br /><font face="sans-serif" size="2">Theentries that match BLAND type in OWNER table and who also have a matching entry in USER table NAME but onlyfor USER_TBL entries with places equal to HOME. </font><br /><br /><font face="sans-serif" size="2">DELETE FROM SP.TST_USER_TBLWHERE EXISTS</font><br /><font face="sans-serif" size="2"> (SELECT SP.TST_USER_TBL.NAME FROM SP.TST_USER_TBL,SP.TST_OWNER_TBL WHERE TYPE='BLAND' AND PLACE='HOME' AND SP.TST_OWNER_TBL.NAME=SP.TST_USER_TBL.NAME) </font><br/><br /><br /><font face="sans-serif" size="2">Example :</font><br /><br /><font face="sans-serif" size="2"> <b>OWNER_TBL</b> <b>USER_TBL</b></font><br /><br /><font face="sans-serif" size="2"> <u> NAME TYPE PLACE NAME</u></font><br /><font face="sans-serif"size="2">BLAND BLAND WORK BLAND</font><br /><font face="sans-serif"size="2">LARRY BLAND HOME BLAND</font><br /><font face="sans-serif"size="2">MOE BLAND HOME LARRY</font><br /><font face="sans-serif"size="2">CURLY BLAND WORK LARRY</font><br /><font face="sans-serif"size="2">JOE BLAND HOME MOE</font><br /><br /><br /><fontface="sans-serif" size="2">In the end I expect the USER_TBL to <b>not </b>contain the 3 HOME entries. </font><br/><font face="sans-serif" size="2">But what is happening is the whole USER_TBL is empty after the query. </font><br/><br /><font face="sans-serif" size="2">Any ideas or tips.. Thanks.</font>
<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><br /><font face="sans-serif" size="2"> (SELECT <a href="http://sp.tst_user_tbl.name/"target="_blank">SP.TST_USER_TBL.NAME</a> FROM <b>SP.TST_USER_TBL, </b>SP.TST_OWNER_TBLWHERE TYPE='BLAND' AND PLACE='HOME' AND <a href="http://sp.tst_owner_tbl.name/" target="_blank">SP.TST_OWNER_TBL.NAME</a>=<ahref="http://sp.tst_user_tbl.name/" target="_blank">SP.TST_USER_TBL.NAME</a>)</font><br /><br />Right:<br /><br /><font face="sans-serif" size="2">DELETE FROMSP.TST_USER_TBL WHERE EXISTS</font><br /><font face="sans-serif" size="2"> (SELECT <a href="http://sp.tst_user_tbl.name/"target="_blank">SP.TST_USER_TBL.NAME</a> FROM </font><font face="sans-serif" size="2"><ahref="http://sp.tst_owner_tbl.name/" target="_blank">SP.TST_OWNER_TBL</a></font><font face="sans-serif" size="2">WHERE TYPE='BLAND' AND PLACE='HOME' AND <a href="http://sp.tst_owner_tbl.name/" target="_blank">SP.TST_OWNER_TBL.NAME</a>=<ahref="http://sp.tst_user_tbl.name/" target="_blank">SP.TST_USER_TBL.NAME</a>)</font><br /><br />Or:<br /><br />DELETE FROM <font face="sans-serif" size="2"><ahref="http://sp.tst_user_tbl.name/" target="_blank">SP.TST_USER_TBL</a></font> WHERE PLACE = 'HOME' AND NAME IN(SELECT NAME FROM <font face="sans-serif" size="2"><a href="http://sp.tst_owner_tbl.name/" target="_blank">SP.TST_OWNER_TBL</a></font>WHERE TYPE = 'BLAND');<br /><br /><br />Carla O.<br /><br /><div class="gmail_quote">2010/11/30Jeff Bland <span dir="ltr"><<a href="mailto:bland@us.ibm.com" target="_blank">bland@us.ibm.com</a>></span><br/><blockquote class="gmail_quote" style="margin: 0pt 0pt 0pt 0.8ex; border-left:1px solid rgb(204, 204, 204); padding-left: 1ex;"><br /><font face="sans-serif" size="2">I want to delete certainrows from table USER_TBL. </font><br /><font face="sans-serif" size="2">Two tables are involved. USER_TBL and OWNER_TBL.</font><br /><br /><font face="sans-serif" size="2">The entries that match BLAND type in OWNER table and who alsohave a matching entry in USER table NAME but only for USER_TBL entries with places equal to HOME. </font><br /><br/><font face="sans-serif" size="2">DELETE FROM SP.TST_USER_TBL WHERE EXISTS</font><br /><font face="sans-serif" size="2"> (SELECT<a href="http://SP.TST_USER_TBL.NAME" target="_blank">SP.TST_USER_TBL.NAME</a> FROM SP.TST_USER_TBL, SP.TST_OWNER_TBLWHERE TYPE='BLAND' AND PLACE='HOME' AND <a href="http://SP.TST_OWNER_TBL.NAME" target="_blank">SP.TST_OWNER_TBL.NAME</a>=<ahref="http://SP.TST_USER_TBL.NAME" target="_blank">SP.TST_USER_TBL.NAME</a>)</font><br /><br /><br /><font face="sans-serif" size="2">Example :</font><br /><br/><font face="sans-serif" size="2"> <b>OWNER_TBL</b> <b>USER_TBL</b></font><br/><br /><font face="sans-serif" size="2"> <u> NAME TYPE PLACE NAME</u></font><br /><font face="sans-serif" size="2">BLAND BLAND WORK BLAND</font><br /><font face="sans-serif" size="2">LARRY BLAND HOME BLAND</font><br /><font face="sans-serif" size="2">MOE BLAND HOME LARRY</font><br /><font face="sans-serif" size="2">CURLY BLAND WORK LARRY</font><br /><font face="sans-serif" size="2">JOE BLAND HOME MOE</font><br /><br /><br /><font face="sans-serif" size="2">In the end I expect the USER_TBL to <b>not</b>contain the 3 HOME entries. </font><br /><font face="sans-serif" size="2">But what is happening is the wholeUSER_TBL is empty after the query. </font><br /><br /><font face="sans-serif" size="2">Any ideas or tips.. Thanks.</font></blockquote></div><br/>
<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 />
On 2010-11-30, Jeff Bland <bland@us.ibm.com> wrote: > This is a multipart message in MIME format. > --=_alternative 007A6509852577EB_= > Content-Type: text/plain; charset="US-ASCII" > > I want to delete certain rows from table USER_TBL. > Two tables are involved. USER_TBL and OWNER_TBL. delete ... using was invented for this purpose. > In the end I expect the USER_TBL to not contain the 3 HOME entries. > But what is happening is the whole USER_TBL is empty after the query. your subselect is being effected by the table used in the delete. -- ⚂⚃ 100% natural