Below SQL queries below will help to remove duplicate values from the SQL table.
DELETE FROM table
WHERE ID IN (
SELECT ID
FROM table
GROUP BY ID
HAVING COUNT(ID) > 1
);
DELETE FROM [master].[dbo].[EMP]
WHERE EMP_ID NOT IN (
SELECT MAX(EMP_ID) AS MaxRecordID
FROM [master].[dbo].[EMP]
GROUP BY emp_name, EMP_SALARY
)
abhishekPosted Feb 28, 2025, 7:47 AM
Hi, can we also use these ;WITH CTE AS ( SELECT ID FROM ( SELECT ID, ROW_NUMBER() OVER (PARTITION BY Employeename, salary ORDER BY ID DESC ) AS RN --keeps latest records FROM Employees WHERE ISNULL(Employeename, '') <>'' ) AS SUB WHERE RN > 1 -- Marks duplicate records ) DELETE FROM Employees WHERE ID IN (SELECT ID FROM CTE);