Re: DELETE WHERE EXISTS unexpected results

<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/>