Re: DELETE WHERE EXISTS unexpected results - Mailing list pgsql-sql
From | Carla |
---|---|
Subject | Re: DELETE WHERE EXISTS unexpected results |
Date | |
Msg-id | AANLkTinwm7hqZ=8P9p0Czr4S1rrtsaJS62P_Reofh18F@mail.gmail.com Whole thread Raw |
In response to | DELETE WHERE EXISTS unexpected results (Jeff Bland <bland@us.ibm.com>) |
Responses |
Re: DELETE WHERE EXISTS unexpected results
|
List | pgsql-sql |
<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/>