RE: SQL delete based on aggregate function?

What happens with a double subquery like this:


delete from wordfile where word in (select word from (select word, 
count(*) as c from wordfile group by word having c=1))





"Goodbar, Loyd (ETS - Water Valley)" <LGoodbar@xxxxxxxxxxxxxx> 
Sent by: midrange-l-bounces@xxxxxxxxxxxx
01/20/2005 03:39 PM
Please respond to
Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>


To
Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
cc

Subject
RE: SQL delete based on aggregate function?






Let's try it.

create table qtemp/myword (word char(80)) 
Table MYWORD created in QTEMP. 

insert into qtemp/myword select word from lgsql/wordfile2 
group by word having count(*) = 1 
16524 rows inserted in MYWORD in QTEMP. 

delete from lgsql/wordfile2 where exists 
(select word from qtemp/myword) 
94111 rows deleted from WORDFILE2 in LGSQL. 

The where exists... is true for all rows. All rows were removed! Not to
worry, this is a test file anyway, easily recreated.

insert into lgsql/wordfile2 
(stockno, word, fulldesc, location) 
select stockno, word, fulldesc, location from loyd/wordfile2
94111 rows inserted in WORDFILE2 in LGSQL. 

delete from lgsql/wordfile2 where exists 
(select word from qtemp/myword 
 where wordfile2.word=myword.word) 
16524 rows deleted from WORDFILE2 in LGSQL. 


This is the correct outcome! And it ran in under 10 seconds!
Hurray!


Loyd Goodbar
Senior programmer/analyst
BorgWarner
E/TS Water Valley
662-473-5713

-----Original Message-----
From: Richard Casey [mailto:casey_r@xxxxxxxxxxxxxxxx] 
Sent: Thursday, January 20, 2005 09:59
To: Midrange Systems Technical Discussion
Subject: RE: SQL delete based on aggregate function?

Loyd,

Try creating a temporary file containing the words to be deleted and then
use that file in your delete statement.

CREATE TABLE QTEMP/ONEWORD (WORD CHAR(30))
INSERT INTO QTEMP/ONEWORD SELECT WORD FROM WORDFILE2 GROUP BY WORD HAVING
COUNT(*)=1
DELETE FROM WORDFILE2 WHERE EXISTS (SELECT WORD FROM QTEMP/ONEWORD)

Hope this helps!
Richard
-- 
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing 
list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.