[SQL] DELETE WHERE EXISTS unexpected results

[Jeff Bland]

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   WORKBLAND
LARRY   BLAND   HOMEBLAND
MOE BLAND   HOMELARRY
CURLY   BLAND   WORKLARRY
JOE BLAND   HOMEMOE


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.

Re: [SQL] DELETE WHERE EXISTS unexpected results

[Jeff Bland]

The second example you gave worked for me.   Thanks Carla ! 

D. Jeff Bland
z/OS System House Installation and Packaging (zSHIP)
BLAND at IBMUS
bl...@us.ibm.com
http://w3.pok.ibm.com/zos/i95a/
845-435-42108/295-4210
Famous quote: Beauty is in the eye of the beer holder.



From:
Carla 
To:
Jeff Bland/Poughkeepsie/i...@ibmus
Cc:
pgsql-sql@postgresql.org
Date:
12/01/2010 08:05 AM
Subject:
Re: [SQL] DELETE WHERE EXISTS unexpected results
Sent by:
cgourof...@gmail.com



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.

2010/11/30 Jeff Bland 

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_TBLUSER_TBL 

   NAME  TYPEPLACENAME 
BLANDBLANDWORK   
 BLAND 
LARRYBLANDHOME   
 BLAND 
MOEBLANDHOMELARRY 
CURLYBLANDWORK   
 LARRY 
JOEBLANDHOMEMOE 


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.