pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedDELETE WHERE EXISTS unexpected results
4+ messages / 3 participants
[nested] [flat]
* DELETE WHERE EXISTS unexpected results
@ 2010-11-30 22:16 Jeff Bland <bland@us.ibm.com>
2010-12-01 13:05 ` Re: DELETE WHERE EXISTS unexpected results Carla <cgourofino@hotmail.com>
2010-12-02 07:47 ` Re: DELETE WHERE EXISTS unexpected results Jasen Betts <jasen@xnet.co.nz>
0 siblings, 2 replies; 4+ messages in thread
From: Jeff Bland @ 2010-11-30 22:16 UTC (permalink / raw)
To: 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.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: DELETE WHERE EXISTS unexpected results
2010-11-30 22:16 DELETE WHERE EXISTS unexpected results Jeff Bland <bland@us.ibm.com>
@ 2010-12-01 13:05 ` Carla <cgourofino@hotmail.com>
2010-12-01 15:07 ` Re: DELETE WHERE EXISTS unexpected results Jeff Bland <bland@us.ibm.com>
1 sibling, 1 reply; 4+ messages in thread
From: Carla @ 2010-12-01 13:05 UTC (permalink / raw)
To: Jeff Bland <bland@us.ibm.com>; +Cc: pgsql-sql
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@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.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: DELETE WHERE EXISTS unexpected results
2010-11-30 22:16 DELETE WHERE EXISTS unexpected results Jeff Bland <bland@us.ibm.com>
2010-12-01 13:05 ` Re: DELETE WHERE EXISTS unexpected results Carla <cgourofino@hotmail.com>
@ 2010-12-01 15:07 ` Jeff Bland <bland@us.ibm.com>
0 siblings, 0 replies; 4+ messages in thread
From: Jeff Bland @ 2010-12-01 15:07 UTC (permalink / raw)
To: cgourofino@hotmail.com; +Cc: cgourofino@gmail.com; pgsql-sql
The second example you gave worked for me. Thanks Carla !
D. Jeff Bland
z/OS System House Installation and Packaging (zSHIP)
BLAND at IBMUS
bland@us.ibm.com
http://w3.pok.ibm.com/zos/i95a/
845-435-4210 8/295-4210
Famous quote: Beauty is in the eye of the beer holder.
From:
Carla <cgourofino@hotmail.com>
To:
Jeff Bland/Poughkeepsie/IBM@IBMUS
Cc:
pgsql-sql@postgresql.org
Date:
12/01/2010 08:05 AM
Subject:
Re: [SQL] DELETE WHERE EXISTS unexpected results
Sent by:
cgourofino@gmail.com
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 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)
Right:
DELETE FROM SP.TST_USER_TBL WHERE EXISTS
(SELECT SP.TST_USER_TBL.NAME FROM SP.TST_OWNER_TBL WHERE TYPE='BLAND'
AND PLACE='HOME' AND SP.TST_OWNER_TBL.NAME=SP.TST_USER_TBL.NAME)
Or:
DELETE FROM SP.TST_USER_TBL WHERE PLACE = 'HOME' AND NAME IN (SELECT NAME
FROM SP.TST_OWNER_TBL WHERE TYPE = 'BLAND');
Carla O.
2010/11/30 Jeff Bland <bland@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.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: DELETE WHERE EXISTS unexpected results
2010-11-30 22:16 DELETE WHERE EXISTS unexpected results Jeff Bland <bland@us.ibm.com>
@ 2010-12-02 07:47 ` Jasen Betts <jasen@xnet.co.nz>
1 sibling, 0 replies; 4+ messages in thread
From: Jasen Betts @ 2010-12-02 07:47 UTC (permalink / raw)
To: pgsql-sql
On 2010-11-30, Jeff Bland <bland@us.ibm.com> wrote:
> This is a multipart message in MIME format.
> --=_alternative 007A6509852577EB_=
> Content-Type: text/plain; charset="US-ASCII"
>
> I want to delete certain rows from table USER_TBL.
> Two tables are involved. USER_TBL and OWNER_TBL.
delete ... using was invented for this purpose.
> 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.
your subselect is being effected by the table used in the delete.
--
⚂⚃ 100% natural
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2010-12-02 07:47 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-11-30 22:16 DELETE WHERE EXISTS unexpected results Jeff Bland <bland@us.ibm.com>
2010-12-01 13:05 ` Carla <cgourofino@hotmail.com>
2010-12-01 15:07 ` Jeff Bland <bland@us.ibm.com>
2010-12-02 07:47 ` Jasen Betts <jasen@xnet.co.nz>
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