Deleting duplicate rows
e.g:- Suppose that we have a table named "Employees" having auto increment id and we want to delete latest duplicate row. In the following table we would like to delete the latest duplicate salary row, which in this case is [id=3,name=f,salary=200]
DELETE FROM [Data].[dbo].[employees]
WHERE id IN (SELECT TOP(1)id
FROM [Data].[dbo].[employees]
WHERE salary IN (SELECT salary
FROM [Data].[dbo].[employees]
GROUP BY salary
HAVING Count(*) > 1)
ORDER BY id DESC)
After executing this query you will get a table without any duplicate salary
e.g:- Suppose that we have a table named "Employees" having auto increment id and we want to delete latest duplicate row. In the following table we would like to delete the latest duplicate salary row, which in this case is [id=3,name=f,salary=200]
DELETE FROM [Data].[dbo].[employees]
WHERE id IN (SELECT TOP(1)id
FROM [Data].[dbo].[employees]
WHERE salary IN (SELECT salary
FROM [Data].[dbo].[employees]
GROUP BY salary
HAVING Count(*) > 1)
ORDER BY id DESC)
After executing this query you will get a table without any duplicate salary
No comments:
Post a Comment