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