- SET QUOTED_IDENTIFIER ON
- GO
- SET ANSI_NULLS ON
- GO
-
- Create procedure Usp_EffaceDoubValeurs as
-
- DECLARE csr1 CURSOR FOR
- SELECT Col1, Col2, COUNT(*) --mettre les champs en question
- FROM matable -- Indiquer la table
- GROUP BY Col1, Col2 -- mettre les champs en question
- HAVING COUNT(*) > 1 -- Chercher les valeurs qui sont présentes plus d'une fois
- FOR READ ONLY
-
- DECLARE @Col1 INT
- DECLARE @Col2 INT
- DECLARE @count INT
- OPEN csr1
- FETCH NEXT FROM csr1 INTO @Col1, @Col2, @count
-
- WHILE @@FETCH_STATUS <> -1
- BEGIN
- IF @@FETCH_STATUS <> -2
- BEGIN
- SET @count = @count - 1 --efface toutes les valeurs sauf une
- SET ROWCOUNT @count
- DELETE FROM MaTable -- indiquer la table
-
- WHERE Col1 = @Col1 AND Col2 = @Col2 -- indiquer les champs
- END
- FETCH NEXT FROM csr1 INTO @Col1, @Col2, @count
- END
- CLOSE csr1
- DEALLOCATE csr1
- SET ROWCOUNT 0
-
-
-
-
-
-
-
- GO
-
- SET QUOTED_IDENTIFIER OFF
- GO
- SET ANSI_NULLS ON
- GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
Create procedure Usp_EffaceDoubValeurs as
DECLARE csr1 CURSOR FOR
SELECT Col1, Col2, COUNT(*) --mettre les champs en question
FROM matable -- Indiquer la table
GROUP BY Col1, Col2 -- mettre les champs en question
HAVING COUNT(*) > 1 -- Chercher les valeurs qui sont présentes plus d'une fois
FOR READ ONLY
DECLARE @Col1 INT
DECLARE @Col2 INT
DECLARE @count INT
OPEN csr1
FETCH NEXT FROM csr1 INTO @Col1, @Col2, @count
WHILE @@FETCH_STATUS <> -1
BEGIN
IF @@FETCH_STATUS <> -2
BEGIN
SET @count = @count - 1 --efface toutes les valeurs sauf une
SET ROWCOUNT @count
DELETE FROM MaTable -- indiquer la table
WHERE Col1 = @Col1 AND Col2 = @Col2 -- indiquer les champs
END
FETCH NEXT FROM csr1 INTO @Col1, @Col2, @count
END
CLOSE csr1
DEALLOCATE csr1
SET ROWCOUNT 0
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO