RE: SQL delete based on aggregate function?

How about:
delete from wordfile2 where word in
 (select word from wordfile2 
  group by word
  having count(*) = 1
 ) 


Eric DeLong
Sally Beauty Company
MIS-Project Manager (BSG)
940-898-7863 or ext. 1863



-----Original Message-----
From: Goodbar, Loyd (ETS - Water Valley)
[mailto:LGoodbar@xxxxxxxxxxxxxx]
Sent: Thursday, January 20, 2005 8: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

 

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