RE: SQL delete based on aggregate function?

No problem - I figured from reading the first statement what would happen
(since it wasn't conditioned), that's what the "let's try" part was for. 

I also do a select before doing a delete. That's what prompted this
discussion. I had a select statement that worked pretty well (with temp
as... select from wordfile2 join temp...) until I realized it would not work
for delete. As Vern pointed out, the WITH AS clause is not allowed with
update or delete.

All of my searching was to find a fast select statement that was also
compatible for deletes. I was so hung on the idea of performing a single SQL
to do the delete, I forgot about the temp table idea.

Thanks for the kick!

Loyd

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Richard Casey
Sent: Thursday, January 20, 2005 4:07 PM
To: Midrange Systems Technical Discussion
Subject: 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