Deleting columns using count(*) 1

[Azathoth]

My table looks something like this:

|-----------------------------------------------------|
|unit_number sub_rule_id num_donations |
|-----------------------------------------------------|
| 1 5 3 |
| 1 6 2 |
| 2 5 3 |
| 3 7 2 |
| 3 7 2 |
|-----------------------------------------------------|

Each sub_rule_id has a unit_number and num_donations associated with it, and this num_donations must be met in order for the rule to be valid. If that number is not met, the row in the database can be deleted. In the above example, the first three rows would be deleted (ie. sub_rule_id 5 does not appear three times for the same unit). The last two rows would remain, because there are two instances of the sub_rule_id 7 for the same unit number. In trying to code this (the table's name is #result), I came up with:

delete from
#result
where
#result.current_donation_only = 0 and
(select
count(*)
from
#result rr
where
(#result.unit = rr.unit
and r.sub_rule_id = rr.sub_rule_id) < num_donations

Obviously the syntax is wrong...help please?

 

[PHV]

Have you tried something like this ?
delete from #result A
where A.current_donation_only = 0 and exists(
Select B.unit, B.sub_rule_id, B.num_donations, Count(*)
From #result B
Where B.unit=A.unit And B.sub_rule_id=A.sub_rule_id
Group by unit, sub_rule_id, num_donations
Having Count(*)<num_donations
)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244