logo
  • Home
  • My Works
  • Downloads
  • Personal
  • Guest Book
  • About Me

Delete duplicate record from a table in SQL Server

Published by Rafeeque B Muhammad on Saturday, March 6, 2010 12:00 AM
Updated on Saturday, March 6, 2010 12:00 AM


 

Look at this given scenario, I have one old table which didn’t have any primary key, I am thinking to alter my table with a complex primary key. I have a lot of data against this constraint, so that I wanted to remove the duplicated records, but I need to keep one of those.

eg.

Create a sample table

CREATE TABLE MenuItem(MenuId int,UserId int,Name varchar(100))

Insert some sample data

INSERT INTO MenuItem VALUES (1,1,'Menu1');

INSERT INTO MenuItem VALUES (1,1,'Menu2');

INSERT INTO MenuItem VALUES (2,1,'Menu3');

INSERT INTO MenuItem VALUES (4,1,'Menu4');

INSERT INTO MenuItem VALUES (3,1,'Menu5');

INSERT INTO MenuItem VALUES (2,1,'Menu6');

INSERT INTO MenuItem VALUES (3,1,'Menu7');

 

Script to delete duplicate records

CREATE TABLE #tmpMenuItem(

IdentityColumn int IDENTITY,--identityfying column

MenuId int,UserId int,Name varchar(100))

 

INSERT INTO #tmpMenuItem(MenuId,UserId,Name)

Select MenuId,UserId,Name

From MenuItem Where MenuId IN (Select MenuId from MenuItem

Group By MenuId,UserId Having COUNT(*) >1)

     

DELETE From MenuItem Where MenuId IN (Select MenuId from MenuItem

Group By MenuId,UserId Having COUNT(*) >1)

 

INSERT INTO MenuItem(MenuId,UserId,Name)

SELECT  MenuId,UserID,Name FROM #tmpMenuItem

Where IdentityColumn IN (Select Min(IdentityColumn) DisplayId from #tmpMenuItem

Group By MenuId,UserId Having COUNT(*) >1)

     

DROP TABLE #tmpMenuItem

 

Then if you will look at output you can see its deleted the duplicate items, just will keep one of the item which is Min(MenuId) – probably first one.

Logic,

We will just keep the duplicated data in one temporary table (also we can have table type variable to keep the data) and then delete the duplicated data from original table. Once its cleared from original table then try to find out the data from temporary table whatever the condition we required and insert into original table.

The tricky part is this also it’s a simple query.

Select Min(IdentityColumn) DisplayId from #tmpMenuItem

Group By MenuId,UserId Having COUNT(*) >1

If you want to keep some specific data, supposed if we have some field like CreatedDate in the same table, then we can have the order by clause while inserting into the temporary table.

Eg.

INSERT INTO #tmpMenuItem(MenuId,UserId,Name,CreatedDate)

Select MenuId,UserId,Name,CreatedDate

From MenuItem Where MenuId IN (Select MenuId from MenuItem

Group By MenuId,UserId Having COUNT(*) >1)

order by CreatedDate

Technical Articles

  • All
  • .NET Framework
  • ASP.NET
  • C#
  • Win Forms
  • WCF
  • SharePoint(WSS 3.0)
  • SQL Server
  • TSQL
  • Oracle
  • PL/SQL
  • Performance and Tuning
  • Application and Tools
  • Outlook programming
  • HTML/HTML5/XHTML/DHTML/JAVAScript/CSS
  • Windows Services
  • Web Services
  • Security
  • Web
  • General
  • Architecture

Technical Books

  • All
  • C# Threading
  • WCF

Other Links

  • Important Links

Techies Craft © 2014 | Privacy Policy