From: | Jeff Bland <bland(at)us(dot)ibm(dot)com> |
---|---|
To: | cgourofino(at)hotmail(dot)com |
Cc: | cgourofino(at)gmail(dot)com, pgsql-sql(at)postgresql(dot)org |
Subject: | Re: DELETE WHERE EXISTS unexpected results |
Date: | 2010-12-01 15:07:16 |
Message-ID: | OF6352A213.59D91085-ON852577EC.0052E5C4-852577EC.00530F52@us.ibm.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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(at)us(dot)ibm(dot)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(at)hotmail(dot)com>
To:
Jeff Bland/Poughkeepsie/IBM(at)IBMUS
Cc:
pgsql-sql(at)postgresql(dot)org
Date:
12/01/2010 08:05 AM
Subject:
Re: [SQL] DELETE WHERE EXISTS unexpected results
Sent by:
cgourofino(at)gmail(dot)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(at)us(dot)ibm(dot)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.
From | Date | Subject | |
---|---|---|---|
Next Message | Jasen Betts | 2010-12-02 07:47:09 | Re: DELETE WHERE EXISTS unexpected results |
Previous Message | Chang Chao | 2010-12-01 14:31:33 | Re: How strings are sorted by LC_COLLATE specifically? |