Delete duplicate record – SQL

Delete duplicate record from a table.

create table

CREATE TABLE [dbo].[Myinfo](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Name] [nchar](50) NULL,
[Salary] [int] NULL,
CONSTRAINT [PK_Myinfo] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

insert into [mydata].[dbo].[Myinfo] values (‘aaa’,111)
insert into [mydata].[dbo].[Myinfo] values (‘aaa’,111)
insert into [mydata].[dbo].[Myinfo] values (‘aaa’,111)

Solution Delete duplicate records:

1. using create temptable insert all distinct records into it

Select Distinct Name, Salary Into MyNewTable From [mydata].[dbo].[Myinfo];
Truncate Table [mydata].[dbo].[Myinfo];
Insert Into [mydata].[dbo].[Myinfo] Select * From MyNewTable;
Drop Table MyNewTable;

2.using CTE (Common table expression)

;WITH CTE AS
(
SELECT Id,Name,Salary,ROW_NUMBER() OVER(PARTITION BY Name,Salary ORDER BY Id) AS Row
FROM [mydata].[dbo].[Myinfo]
)
DELETE FROM CTE
WHERE Row > 1

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s