From: Jeff Bland <bland@us.ibm.com>
To: pgsql-sql@postgresql.org
Subject: DELETE WHERE EXISTS unexpected results
Date: Tue, 30 Nov 2010 17:16:53 -0500
Message-ID: <OF34A9AF8F.539E0789-ON852577EB.0077F498-852577EB.007A650B@us.ibm.com> (raw)
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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: bland@us.ibm.com
Subject: Re: DELETE WHERE EXISTS unexpected results
In-Reply-To: <OF34A9AF8F.539E0789-ON852577EB.0077F498-852577EB.007A650B@us.ibm.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox