RE: SQL delete based on aggregate function?

The WITH AS was my very first shot at this, and one I expected to work.
That'll show me. I was unprepared for the ensuing saga!

There must be some technicality why WITH AS doesn't work for update or
delete. I imagine it's because it's part of the same statement, and is not
isolated from changes. IOW, changes to the table would change the temp
table. That isn't how I understand WITH AS to work, as it should be an
in-memory table.

This must be some of that "black box" stuff Joe complains about so much. :)

Loyd

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of vhamberg@xxxxxxxxxxx
Sent: Thursday, January 20, 2005 5:26 PM
To: Midrange Systems Technical Discussion
Subject: RE: SQL delete based on aggregate function?

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 
-- 
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.