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
