-
DELETE items where COUNT > 1 (MS SQL)
I cannot find an easy way to DELETE items which are > 1 time in my table (i am working with MS SQL 2000)
Code:
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
Code:
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
Code:
id serial isOk
-------------------
8 AAA 1
9 CCC 1
3 BBB 0
5 dfds 0
7 fdfd 0
thank you for helping
-
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) ?
-
no thats wrong too , it doesnt work
-
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
-
In your sample data, how did the value of isOK change for id's 8 and 9?
-
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
-
Originally Posted by nosepicker
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
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|