Delete records with a count(field) > 9

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Delete records with a count(field) > 9

Author

Topic

mlevier
Starting Member

33 Posts

Posted - 2007-09-13 : 15:37:03

I'm trying to delete all records from a table where the count(ANI) > 9. What that means if the ANI shows up more than 9 times I need to get rid of all of them that have the same ANI. I'm using a statement and can't get it to work.

delete
from #feeder03
group by ani
having ( count(ani) > 9 )

Any help would be great!
Thanks in advance!

X002548
Not Just a Number

15586 Posts

Posted - 2007-09-13 : 15:40:00

What does this give you?

SELECT ANIME, COUNT(*) FROM #feeder03 GROUP BY ANIME

?/??/?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-13 : 15:46:08

Are you using Microsoft SQL Server?
I just wandered because the syntax is so far away...

E 12°55'05.25"
N 56°04'39.16"

mlevier
Starting Member

33 Posts

Posted - 2007-09-13 : 15:47:23

But then how can I delete them from that select statemnent?

X002548
Not Just a Number

15586 Posts

Posted - 2007-09-13 : 15:52:19

quote:
Originally posted by mlevier

But then how can I delete them from that select statemnent?

I want you to tell us if that returns anything

It's like a starting point

What's the PK of the table?

DELETE ROM #feeder03 o
WHERE EXISTS (
SELECT ANI FROM #feeder03 i
WHERE o.PK = i.PK
GROUP BY ANI
HAVING COUNT(ANI) > 9)

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

mlevier
Starting Member

33 Posts

Posted - 2007-09-13 : 15:53:01

Yes, I am working in SQL Server 2005. I got this statement to only select the calls that are greater than 9. I need to know how to delete those records from another table of even delete them from the table that they are in.

select
Count(#feeder.ani) as calls
from #feeder
group by ANI
having ( count(ani) > 9 )

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-13 : 15:53:37

[code]DELETE f
FROM #Feeder03 AS f
INNER JOIN (
SELECT Ani
FROM #Feeder03
GROUP BY Ani
HAVING COUNT(*) > 9
) AS u ON u.Ani = f.Ani[/code]

E 12°55'05.25"
N 56°04'39.16"

mlevier
Starting Member

33 Posts

Posted - 2007-09-13 : 15:59:40

It runs but it doesn't delete them

mlevier
Starting Member

33 Posts

Posted - 2007-09-13 : 16:04:36