RE: SQL delete based on aggregate function?

Actually the aggregate function will be done once and the result used
123,000.  Try this to test the timing.

Select count(*) from wordfile2 where word in
 (select word from wordfile2
  group by word
  having count(*) = 1
 )

My guess is that you will have an answer in less than 1 minute.  Let us know
the timing.

Don Tully
Tully Consulting LLC

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


This does work but will take forever. For each row being processed (all
123,000), a temporary list (the where word in... clause) must be built. So,
123,000 * 123,000 records are being processed for this query. I was looking
for something quicker.

Thanks,
Loyd

Loyd Goodbar
Senior programmer/analyst
BorgWarner
E/TS Water Valley
662-473-5713
-----Original Message-----
From: DeLong, Eric [mailto:EDeLong@xxxxxxxxxxxxxxx]
Sent: Thursday, January 20, 2005 08:20
To: 'Midrange Systems Technical Discussion'
Subject: 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.


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