# Miscellaneous > Ask an Expert >  DELETE  items where COUNT > 1 (MS SQL)

## anselme

I cannot find an easy  way to DELETE items which are > 1 time in my table (i am working with MS SQL 2000)



```
  
id	serial	isOk
-------------------
2	AAA	1
3	BBB	0
5	dfds	0
6	CCC	1
7	fdfd	 0
8	AAA	0
9	CCC	0
```

I want to DELETE each Row  IN



```
 

SELECT doublons.serial, Count(doublons.serial) AS  2Times
FROM doublons
GROUP BY doublons.serial
HAVING  Count(doublons.serial)>1
```

and WHERE isOK = 0

in my exemple , after deleting, my table must look like



```
 

id	serial	isOk
-------------------
8	AAA	1
9	CCC	1
3	BBB	0
5	dfds	0
7	fdfd	0
```

thank you for helping

----------


## anselme

i found that way 




> DELETE * FROM Doublons
> WHERE doublons.serial < ANY
>  (
> 	SELECT DISTINCT doublons.serial
> 	FROM doublons, doublons AS doublons_1
> 	WHERE Exists
>  	(
> 		SELECT  doublons.serial
> 		FROM  doublons AS doublons_2
> ...


do you think it is the best method for large database (a few 1000 of rows) ?

----------


## anselme

no thats wrong too , it doesnt work

----------


## skhanal

Does this work

delete a 
from doublons a
where a.serial in
(SELECT doublons.serial
FROM doublons
GROUP BY doublons.serial
HAVING  Count(doublons.serial)>1)
and a.isok=0

----------


## nosepicker

In your sample data, how did the value of isOK change for id's 8 and 9?

----------


## nosepicker

For a large table, using a JOIN might be faster:

DELETE doublons 
FROM doublons JOIN 
(SELECT serial FROM doublons GROUP BY serial HAVING COUNT(*) > 1) AS A 
ON doublons.serial = A.serial 
WHERE doublons.isOK = 0

----------


## anselme

> In your sample data, how did the value of isOK change for id's 8 and 9?


there is 2 way to enter datas : automatic and manual
if datas are entered manually isOk = 0
if automaticly  isOk = 1

automatic mode must overwrite manual mode

------------------
that code works fine (found on another forum)

delete d from  Doublons d join
(
SELECT Doublons.serial FROM Doublons
GROUP BY Doublons.serial
HAVING  Count(Doublons.serial)>1
) as tm
on tm.serial=d.serial
where d.isOK=0


thank you for your help

----------

