Re: DELETE WHERE EXISTS unexpected results

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 <http://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
<http://sp.tst_owner_tbl.name/>=SP.TST_USER_TBL.NAME<http://sp.tst_user_tbl.name/>)

Right:

DELETE FROM SP.TST_USER_TBL WHERE EXISTS
(SELECT SP.TST_USER_TBL.NAME <http://sp.tst_user_tbl.name/> FROM
SP.TST_OWNER_TBL <http://sp.tst_owner_tbl.name/> WHERE TYPE='BLAND' AND
PLACE='HOME' AND SP.TST_OWNER_TBL.NAME <http://sp.tst_owner_tbl.name/>=
SP.TST_USER_TBL.NAME <http://sp.tst_user_tbl.name/>)

Or:

DELETE FROM SP.TST_USER_TBL <http://sp.tst_user_tbl.name/> WHERE PLACE =
'HOME' AND NAME IN (SELECT NAME FROM
SP.TST_OWNER_TBL<http://sp.tst_owner_tbl.name/>WHERE TYPE = 'BLAND');

Carla O.

2010/11/30 Jeff Bland <bland(at)us(dot)ibm(dot)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.