DELETE WHERE EXISTS unexpected results

From: Jeff Bland <bland(at)us(dot)ibm(dot)com>
To: pgsql-sql(at)postgresql(dot)org
Subject: DELETE WHERE EXISTS unexpected results
Date: 2010-11-30 22:16:53
Message-ID: OF34A9AF8F.539E0789-ON852577EB.0077F498-852577EB.007A650B@us.ibm.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

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.

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Samuel Gendler 2010-12-01 03:47:29 aggregation question
Previous Message Isaac Dover 2010-11-30 20:03:04 Re: indexing longish string