Обсуждение: 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>
You don't have to include the name of the "delete table" in the subselect.
Wrong:
DELETE FROM SP.TST_USER_TBL WHERE EXISTS
(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)
Right:
DELETE FROM SP.TST_USER_TBL WHERE EXISTS
(SELECT SP.TST_USER_TBL.NAME FROM SP.TST_OWNER_TBL WHERE TYPE='BLAND' AND PLACE='HOME' AND SP.TST_OWNER_TBL.NAME=SP.TST_USER_TBL.NAME)
Or:
DELETE FROM SP.TST_USER_TBL WHERE PLACE = 'HOME' AND NAME IN (SELECT NAME FROM SP.TST_OWNER_TBL WHERE TYPE = 'BLAND');
Carla O.
Wrong:
DELETE FROM SP.TST_USER_TBL WHERE EXISTS
(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)
Right:
DELETE FROM SP.TST_USER_TBL WHERE EXISTS
(SELECT SP.TST_USER_TBL.NAME FROM SP.TST_OWNER_TBL WHERE TYPE='BLAND' AND PLACE='HOME' AND SP.TST_OWNER_TBL.NAME=SP.TST_USER_TBL.NAME)
Or:
DELETE FROM SP.TST_USER_TBL WHERE PLACE = 'HOME' AND NAME IN (SELECT NAME FROM SP.TST_OWNER_TBL WHERE TYPE = 'BLAND');
Carla O.
2010/11/30 Jeff Bland <bland@us.ibm.com>
I want to delete certain rows from table USER_TBL.
Two tables are involved. USER_TBL and OWNER_TBL.
The entries that match BLAND type in OWNER table and who also have a matching entry in USER table NAME but only for USER_TBL entries with places equal to HOME.
DELETE FROM SP.TST_USER_TBL WHERE EXISTS
(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)
Example :
OWNER_TBL USER_TBL
NAME TYPE PLACE NAME
BLAND BLAND WORK BLAND
LARRY BLAND HOME BLAND
MOE BLAND HOME LARRY
CURLY BLAND WORK LARRY
JOE BLAND HOME MOE
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.
Any ideas or tips.. Thanks.
<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