RE: SQL delete based on aggregate function?

[vhamberg@xxxxxxxxxxx]

Very cool!

According to the manual (RTFM), the DELETE statement does not have the WITH AS 
available. Only a SELECT. Not UPDATE either. 

Vern

-------------- Original message -------------- 

> 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