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