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
WHERE doublons.Serial = doublons_2.Serial
AND doublons.isOk=0
)
)

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