RE: SQL delete based on aggregate function?

["Richard Casey" <casey_r@xxxxxxxxxxxxxxxx>]

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


-----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 9:01 AM
To: Midrange Systems Technical Discussion
Subject: SQL delete based on aggregate function?


I have a dilemma here.



I have a file consisting of a stock number and a single word. Data looks
like this:

STOCK NUMBER               WORD IN DESCRIPTION

T01-01299                  LOCATOR

T01-01299                  DIAMOND

T01-01299                  12R10430A

T01-01299                  F1

T01-01299                  DET

T01-01299                  27

T15-05602                  CYLINDER

T15-05602                  NECOA

T15-05602                  WINDER

T15-05602                  TERMINAL

T15-05602                  CLAMP



Occasionally we parse item descriptions by word to analyze for duplicates
and clean up description verbiage. The file contains multiple words for each
stock number, and has 123,000 records.



I want to remove any instances where the word only appears once in the file,
based on the following select:



Select word, count(*) from wordfile2

Group by word having count(*) = 1



I can't figure out how to cleanly remove these records. I would love to use
something like this:



with temp as

(

select word, count(*) from wordfile2

group by word

having count(*) = 1

)

delete from wordfile2 where exists (select word from temp)



but it says "Keyword DELETE not expected. Valid tokens: ( SELECT."



The only way I could make this work was:



delete from wordfile2 where word in

(select word from (select word, count(*) from

wordfile2 group by word having count(*)=1) as word)



But that takes forever, since each row causes a rebuild of the in... clause.



Do any of you SQL gurus know of a better way?



Thanks,

Loyd

Loyd Goodbar
Senior programmer/analyst
BorgWarner
E/TS Water Valley
662-473-5713