delete using sub query
-
i am trying to delete duplicate firstname using Sub Query. I am sending the query please correct with my query
DELETE FROM #Contact
WHERE CAST(FirstName AS INT) IN (SELECT COUNT(FirstName) FROM #Contact GROUP BY FirstName HAVING COUNT(*)>1 )
-
This is a good way using a CTE. Even though you delete from the CTE, the underlying table is modified:
IF OBJECT_ID('tempdb..#Contact') IS NOT NULL
DROP TABLE #Contact;
CREATE TABLE #Contact (FirstName Varchar(30));
INSERT INTO #Contact VALUES ( 'Fred' );
INSERT INTO #Contact VALUES ( 'Fred' );
INSERT INTO #Contact VALUES ( 'Fred' );
INSERT INTO #Contact VALUES ( 'Bill' );
INSERT INTO #Contact VALUES ( 'Harry' );
INSERT INTO #Contact VALUES ( 'Harry' );
SELECT * FROM #Contact;
WITH Duplicates AS
(
SELECT FirstName,
ROW_NUMBER() OVER (PARTITION BY FirstName ORDER BY FirstName) as DupCount
FROM #Contact
)
DELETE FROM Duplicates WHERE DupCount > 1;
SELECT * FROM #Contact;
-
My favorite way to get rid of dupes is with a Common Table Expression (CTE).
WITH GroupedDupes AS
(SELECT RowNumber = ROW_NUMBER() OVER (PARTITION BY FirstName ORDER BY FirstName)
, FirstName
FROM #Contact)
SELECT * FROM GroupedDupes ; -- Use to display duplicate rows
--DELETE FROM GroupedDupes WHERE RowNumber > 1 ; -- Use to delete duplicate rows
-
I got the solution what i wanted but not a good solution
SET ROWCOUNT 1
WHILE 1=1
BEGIN
DELETE FROM #Contact
WHERE FirstName IN (SELECT FirstName FROM #Contact GROUP BY FirstName
HAVING COUNT(*)>1)
IF @@ROWCOUNT=0
BREAK
END
SET ROWCOUNT 1
-
Well - that'll be extremely slow.
Why don't you like the CTE solution?
-
Smash125 (8/7/2012)
I got the solution what i wanted but not a good solutionSET ROWCOUNT 1
WHILE 1=1
BEGIN
DELETE FROM #Contact
WHERE FirstName IN (SELECT FirstName FROM #Contact GROUP BY FirstName
HAVING COUNT(*)>1)
IF @@ROWCOUNT=0
BREAK
END
SET ROWCOUNT 1
Also, understand that the way you are using SET ROWCOUNT to control the number of rows affected by the DELETE statement is being depreciated and will not be supported in a future version of SQL Server. Using the CTE version is much more versatile and scales well.
-
Yes i do agree with you guys.Using ranking function makes life easy when deleting duplicate rows
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply