forum.venkateswarlu.net
      Register      Login
Duplicate record deletion(Rercently asked interview question)

4 replies to this topic

Harikrishna Sikhakolli #1
Member
108 Points
 Posted on 04 Apr 2012 06:19 AM IST How to delete duplicate records from the table without using
CTE(common table Expression) 
SQL Server     1004 views     Reply to this topic
Harikrishna Sikhakolli #2
Member
108 Points
 Replied on 09 Apr 2012 02:36 PM IST SELECT DISTINCT * INTO #TEMP1 FROM EMP
--HERE ALL THE DISTINCT RECORDS MOVED INTO TEMPORARY TABLE.
TRUNCATE TABLE EMP
--HERE ALL THE RECORDS FROM THE EMP TABLE ARE REMOVED.
INSERT INTO EMP FROM #TEMP1
--HERE DATA IS INSERTED INTO EMP TABLE FROM A TEMPORARY TABLE. 
Reply to this topic
Abhishek Boga #3
Member
14 Points
 Replied on 24 May 2013 12:46 PM IST

;With DupDel as

(Select row_number() over (partiton by column1,column2 order by column3 (must be primary key)) ID,column1,column2 from tbl_name)

Delete from DupDel where ID>1

 
Reply to this topic
Abhishek Boga #4
Member
14 Points
 Replied on 24 May 2013 12:51 PM IST

Delete Employee where Employee.ID < (select MAX(id) from Employee e2 where Employee.FirstName=e2.FirstName and Employee.Address=e2.Address and Employee.LastName=e2.LastName)

 
Reply to this topic
Cherukuri Venkateswarlu #5
Member
140 Points
 Replied on 12 Dec 2013 10:34 AM IST

/* Delete Duplicate records */

WITH CTE (COl1,Col2, DuplicateCount)

AS

(

SELECT COl1,Col2,

ROW_NUMBER() OVER(PARTITION BY COl1,Col2 ORDER BY Col1) AS DuplicateCount

FROM DuplicateRcordTable

)

DELETE

FROM CTE

WHERE DuplicateCount > 1

GO

 
Reply to this topic