August 18, 2014 at 11:39 am
I have created a table Emp_Salary
Create Table Employee_Salary
(
EmployeeID Int,
FirstName Varchar(20),
Salary Money
)
and inserted some records into the table. Some of them are duplicate records. Now, I want to remove all the duplicate records present in the table.The query I wrote was:
Delete from Employee_Salary
WHERE EXISTS (select FirstName,COUNT(FirstName) from Employee_Salary
GROUP BY FirstName
HAving COUNT(FirstName) > 1)
But this is deleting all the records from the table..How can we just delete the duplicate records only?
August 18, 2014 at 11:51 am
August 18, 2014 at 12:12 pm
You can try this article that shows how to do that.
http://jasonbrimhall.info/?s=duplicate+cte
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
August 18, 2014 at 12:15 pm
If you try this, I'd suggest to use one of the alternative methods posted in the discussion of the article.
August 19, 2014 at 6:24 pm
Something like this ?
WITH myCTE
AS (
SELECT row_number() over (partition by EmployeeID, Firstname,Salary
ORDER BY EmployeeID, FirstName, Salart ) as Colm1,*
from Employee_salary
)
DELETE From myCTE where Colm1> 1
----------------------------------------------------
August 27, 2014 at 9:19 am
I ran into this method in an Itzik Ben-Gan article, which looks more complicated, but of course somehow manages to run a bit faster where I've tested it against a single CTE with ROW_NUMBER() involved, I guess because there's no ORDER BY used anywhere?
;WITH dupe1
AS ( SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS RN ,
[FirstName]
FROM dbo.Emp_Salary
),
dupe2
AS ( SELECT [FirstName] ,
MAX(RN) AS [MAX]
FROM dupe1
GROUP BY [FirstName]
HAVING COUNT(*) > 1
)
SELECT d1.*
--DELETE d1
FROM dupe1 d1
INNER JOIN dupe2 d2
ON d1.[FirstName] = d2.[FirstName]
AND d1.RN < d2.[MAX];
August 27, 2014 at 3:15 pm
sqldriver (8/27/2014)
I ran into this method in an Itzik Ben-Gan article, which looks more complicated, but of course somehow manages to run a bit faster where I've tested it against a single CTE with ROW_NUMBER() involved, I guess because there's no ORDER BY used anywhere?
;WITH dupe1
AS ( SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS RN ,
[FirstName]
FROM dbo.Emp_Salary
),
dupe2
AS ( SELECT [FirstName] ,
MAX(RN) AS [MAX]
FROM dupe1
GROUP BY [FirstName]
HAVING COUNT(*) > 1
)
SELECT d1.*
--DELETE d1
FROM dupe1 d1
INNER JOIN dupe2 d2
ON d1.[FirstName] = d2.[FirstName]
AND d1.RN < d2.[MAX];
Another consideration on the performance is that this only compares the first name, which multiple people can of course share. The OP did not respond to my suggestion on if what I posted was a good and simple solution. It seems to me that if there is a employeeID in there and no trust issues with the data, then all you have to do is
WITH myCTE
AS (
SELECT row_number() over (partition by EmployeeID
ORDER BY EmployeeID) as Colm1,*
from Employee_salary
)
DELETE From myCTE where Colm1> 1
But since this will very likely be a one time thing (else there is a bigger problem with the system ) , performance will not be as high on the must have list.
----------------------------------------------------
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply