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 <bl...@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.
