RE: SQL delete based on aggregate function?

Great! Sorry about missing the where clause. I was probably thinking of
this:

delete from lgsql/wordfile2 where word in (select word from qtemp/myword)

I don't know if that would be any faster.

If I'm doing a mass delete, I usually do a "SELECT *" from my table using
the same WHERE clause. That lets me see the records I'm going to be
deleting. Or I can do a "SELECT COUNT(*)" to get a count. That helps protect
against the "oops" factor!!

Richard



-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx]On Behalf Of Goodbar, Loyd (ETS
- Water Valley)
Sent: Thursday, January 20, 2005 4:40 PM
To: Midrange Systems Technical Discussion
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