February 9, 2009 at 3:33 am
Hi Every body,
I have one table Employees
EmpID EmpName
1 aaa
1 aaa
2 aaa
3 bbb
2 bbb
Here in my table dont have primary key to that EMPID how can i eliminate duplicates in my table am using self joins but it is not working could any body help me plz really appreciate to you
Thanks
February 9, 2009 at 3:39 am
A good method for deleting duplicates is using CTE.
it is nicely explained in the msdn site. http://msdn.microsoft.com/en-us/library/ms190766(SQL.90).aspx
Hope the above helps.
Sriram
Sriram
February 9, 2009 at 3:43 am
One common method is to move the data into another table (same structure as emp table)and drop the original emp table and rename the new table.
Create table NewEmp.....
SELECT DISTINCT EmpID,Empname INTO NewEmp
From Emp
Drop Emp
sp_rename 'NewEmp' , 'Emp'
"Keep Trying"
February 9, 2009 at 3:51 am
Hi Thank you for ur reply,
in that EMPID dont have primary key is it possible that one because it is very big database plz help me
February 9, 2009 at 7:14 am
Here is a CTE based solution....
; WITH EmployeesCTE
AS
(
SELECTROW_NUMBER() OVER( PARTITION BY EmpID, EmpName ORDER BY EmpID ) AS RowNumber,
EmpID, EmpName
FROMEmployees
)
DELETE
FROMEmployeesCTE
WHERERowNumber != 1
--Ramesh
February 9, 2009 at 8:58 am
if the table is small then create a new table with the distinct values and then drop the old table and rename the new table to the Old table name.
Thanks -- Vijaya Kadiyala
January 27, 2010 at 5:10 pm
Here is a CTE based solution....
; WITH EmployeesCTE
AS
(
SELECT ROW_NUMBER() OVER( PARTITION BY EmpID, EmpName ORDER BY EmpID ) AS RowNumber,
EmpID, EmpName
FROM Employees
)
DELETE
FROM EmployeesCTE
WHERE RowNumber != 1
--Ramesh
This is elegant...
January 27, 2010 at 5:23 pm
There are already a couple of solutions proposed that use a CTE. I would recommend picking a CTE solution.
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
January 27, 2010 at 10:56 pm
DECLARE @tbl TABLE
(
EMPIDINT,
EmpNameVARCHAR(100)
)
INSERT INTO @tbl
SELECT
1, 'aaa'
UNION ALL
SELECT
1, 'aaa'
UNION ALL
SELECT
2, 'aaa'
UNION ALL
SELECT
3, 'bbb'
UNION ALL
SELECT
2, 'bbb'
DELETE D FROM
(
SELECT
ROW_NUMBER() OVER(PARTITION BY EMPID,EmpName ORDER BY EMPID) AS ROWS,
EMPID,
EmpName
FROM
@tbl
)D
WHERE
ROWS> 1
SELECT * FROM @tbl
Regards,
Mitesh OSwal
+918698619998
January 28, 2010 at 2:21 am
January 28, 2010 at 3:08 am
Because you say your table is big and you don't seem to have the right indexes, you can use the following as an alternative to doing a select distinct or a row_number() over():
Create a new table with a unique index on the colums where you don't want duplicates but use the IGNORE_DUP_KEY = ON option (see http://msdn.microsoft.com/en-us/library/ms188388.aspx). Copy all records from the old table to the new one, drop the old table and rename the new one.
Done.
March 24, 2011 at 5:56 am
I have found good article on deleting duplicate record. You can see T-SQL Query To Delete Duplicate Records Among Identical Rows In A Table....
March 24, 2011 at 11:06 pm
Can be done using temp table
SELECT EmpID,EmpName INTO #temp
FROM Employees
GROUP BY EmpID,EmpName
HAVING COUNT(*)>1
DELETE Employees FROM Employees a INNER JOIN #temp b ON
a.EmpID=b.EmpID
INSERT INTO Employees(EmpID,EmpName)
SELECT EmpID,EmpName FROM #temp
March 24, 2011 at 11:07 pm
Can be done using temp table for your reference
SELECT EmpID,EmpName INTO #temp
FROM Employees
GROUP BY EmpID,EmpName
HAVING COUNT(*)>1
DELETE Employees FROM Employees a INNER JOIN #temp b ON
a.EmpID=b.EmpID
INSERT INTO Employees(EmpID,EmpName)
SELECT EmpID,EmpName FROM #temp
March 25, 2011 at 12:14 pm
A simple way to delete dups that works on versions earlier than 2005 is:
DELETE EMP
FROM Employees EMP
INNER JOIN
(SELECT MIN(EmpID) AS EmpID, EmpName
FROM Employees
GROUP BY EmpName
HAVING COUNT(*) > 1
) AS DUP ON
EMP.EmpName = DUP.EmpName
AND EMP.EmpID <> DUP.EmpID
Todd Fifield
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply