InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comWed, 19 Jun 2013 08:45:37 GMT20http://www.sqlservercentral.com/Forums/Topic652644-338-1.aspxA simple way to delete dups that works on versions earlier than 2005 is:[code="sql"]DELETE EMPFROM Employees EMPINNER 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[/code]Todd FifieldFri, 25 Mar 2011 12:14:45 GMTtfifieldhttp://www.sqlservercentral.com/Forums/Topic652644-338-1.aspxCan be done using temp table for your referenceSELECT EmpID,EmpName INTO #tempFROM EmployeesGROUP BY EmpID,EmpNameHAVING COUNT(*)>1DELETE Employees FROM Employees a INNER JOIN #temp b ONa.EmpID=b.EmpIDINSERT INTO Employees(EmpID,EmpName)SELECT EmpID,EmpName FROM #tempThu, 24 Mar 2011 23:07:51 GMTSmash125http://www.sqlservercentral.com/Forums/Topic652644-338-1.aspxCan be done using temp tableSELECT EmpID,EmpName INTO #tempFROM EmployeesGROUP BY EmpID,EmpNameHAVING COUNT(*)>1DELETE Employees FROM Employees a INNER JOIN #temp b ONa.EmpID=b.EmpIDINSERT INTO Employees(EmpID,EmpName)SELECT EmpID,EmpName FROM #tempThu, 24 Mar 2011 23:06:33 GMTSmash125http://www.sqlservercentral.com/Forums/Topic652644-338-1.aspxI have found good article on deleting duplicate record. You can see [url=http://www.doyouknow.in/Articles/Programming/MS-SQL/T-SQL-Query-To-Delete-Duplicate-Records-Among-Identical-Rows-In-A-Table.aspx]T-SQL Query To Delete Duplicate Records Among Identical Rows In A Table[/url]....Thu, 24 Mar 2011 05:56:35 GMTJimit Shahhttp://www.sqlservercentral.com/Forums/Topic652644-338-1.aspxBecause 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 [url=http://msdn.microsoft.com/en-us/library/ms188388.aspx]http://msdn.microsoft.com/en-us/library/ms188388.aspx[/url]). Copy all records from the old table to the new one, drop the old table and rename the new one.Done.Thu, 28 Jan 2010 03:08:06 GMTwschampheleerhttp://www.sqlservercentral.com/Forums/Topic652644-338-1.aspxYes the CTE technique is the best to eliminate your duplicate values...so just do it!Thu, 28 Jan 2010 02:21:15 GMTDugihttp://www.sqlservercentral.com/Forums/Topic652644-338-1.aspx[code="sql"]DECLARE @tbl TABLE( EMPID INT, EmpName VARCHAR(100))INSERT INTO @tblSELECT1, 'aaa'UNION ALLSELECT1, 'aaa'UNION ALLSELECT2, 'aaa'UNION ALLSELECT3, 'bbb'UNION ALLSELECT2, 'bbb'DELETE D FROM(SELECT ROW_NUMBER() OVER(PARTITION BY EMPID,EmpName ORDER BY EMPID) AS ROWS, EMPID, EmpNameFROM @tbl)DWHERE ROWS > 1SELECT * FROM @tbl [/code]Wed, 27 Jan 2010 22:56:39 GMTMitesh Oswalhttp://www.sqlservercentral.com/Forums/Topic652644-338-1.aspxThere are already a couple of solutions proposed that use a CTE. I would recommend picking a CTE solution.Wed, 27 Jan 2010 17:23:22 GMTSQLRNNRhttp://www.sqlservercentral.com/Forums/Topic652644-338-1.aspxHere is a CTE based solution....; WITH EmployeesCTEAS( SELECT ROW_NUMBER() OVER( PARTITION BY EmpID, EmpName ORDER BY EmpID ) AS RowNumber, EmpID, EmpName FROM Employees)DELETEFROM EmployeesCTEWHERE RowNumber != 1--RameshThis is elegant...Wed, 27 Jan 2010 17:10:55 GMTrcassis-1001343http://www.sqlservercentral.com/Forums/Topic652644-338-1.aspxif 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 Kadiyalahttp://dotnetvj.blogspot.comMon, 09 Feb 2009 08:58:46 GMTVijaya Kadiyalahttp://www.sqlservercentral.com/Forums/Topic652644-338-1.aspxHere is a CTE based solution....[code]; WITH EmployeesCTEAS( SELECT ROW_NUMBER() OVER( PARTITION BY EmpID, EmpName ORDER BY EmpID ) AS RowNumber, EmpID, EmpName FROM Employees)DELETEFROM EmployeesCTEWHERE RowNumber != 1[/code]Mon, 09 Feb 2009 07:14:49 GMTRamesh Saivehttp://www.sqlservercentral.com/Forums/Topic652644-338-1.aspxHi 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 meMon, 09 Feb 2009 03:51:14 GMTkumar99mshttp://www.sqlservercentral.com/Forums/Topic652644-338-1.aspxOne 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 NewEmpFrom EmpDrop Empsp_rename 'NewEmp' , 'Emp'Mon, 09 Feb 2009 03:43:49 GMTChiragNShttp://www.sqlservercentral.com/Forums/Topic652644-338-1.aspxA 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).aspxHope the above helps.SriramMon, 09 Feb 2009 03:39:24 GMTSriram-288748http://www.sqlservercentral.com/Forums/Topic652644-338-1.aspxHi Every body,I have one table EmployeesEmpID EmpName1 aaa1 aaa2 aaa3 bbb2 bbbHere 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 youThanksMon, 09 Feb 2009 03:33:45 GMTkumar99ms