In this article I am going to explain How to delete - Remove duplicate record or rows from table in sql server with example. Sometimes you may want to delete all duplicate recoeds - rows in table. So I am explaining details with live example:
Delete/Romove Duplicate record/rows Using Sub Query:
-----------------Delete - Remove duplicate row/records Using Sub Query ------------------------
DELETE Employee FROM (
SELECT EmppId, EmpName, Salary,
ROW_NUMBER() OVER (PARTITION BY EmppId, EmpName, Salary ORDER BY EmppId) AS 'RowNumber'
FROM [dbo].[tblEmployee]
) AS Employee
WHERE RowNumber > 1
Get SQL Scripts for Create Tables and Insert Data for demo:
---------------Create table ------------
CREATE TABLE [dbo].[tblEmployee]
(
[EmppId] [int] NULL,
[EmpName] [varchar](50) NULL,
[Salary] [float] NULL
) ON [PRIMARY]
--------------------------------- Insert record into table------------------------
INSERT [dbo].[tblEmployee] ([EmppId], [EmpName], [Salary]) VALUES (1, N'ithubcity', 100)
INSERT [dbo].[tblEmployee] ([EmppId], [EmpName], [Salary]) VALUES (1, N'ithubcity', 100)
INSERT [dbo].[tblEmployee] ([EmppId], [EmpName], [Salary]) VALUES (1, N'ithubcity', 100)
INSERT [dbo].[tblEmployee] ([EmppId], [EmpName], [Salary]) VALUES (1, N'ithubcity', 100)
INSERT [dbo].[tblEmployee] ([EmppId], [EmpName], [Salary]) VALUES (2, N'blog', 500)
INSERT [dbo].[tblEmployee] ([EmppId], [EmpName], [Salary]) VALUES (2, N'blog', 500)
INSERT [dbo].[tblEmployee] ([EmppId], [EmpName], [Salary]) VALUES (3, N'blog', 522)