Geek Answers Handbook

Delete multiple duplicate rows in a table

I am sure it was asked before, but it was difficult for me to find it.

I have several groups of duplicates in one table (3 records for one, 2 for another, etc.) - several rows where there are more than 1.

Below I tried to remove them, but I need to run the script for many duplicates:

set rowcount 1
delete from Table
where code in (
  select code from Table 
  group by code
  having (count(code) > 1)
)
set rowcount 0

It works well. I need to run this for each group of duplicates, and then it removes only 1 (which is all I need right now).

I appreciate your help / comments!

+5
source share
4 answers

If you have a key column in a table, you can use it to uniquely identify "different" rows in a table.

, . - .....

create table #TempTable
(
    ID int identity(1,1) not null primary key,
    SomeData varchar(100) not null
)

insert into #TempTable(SomeData) values('someData1')
insert into #TempTable(SomeData) values('someData1')
insert into #TempTable(SomeData) values('someData2')
insert into #TempTable(SomeData) values('someData2')
insert into #TempTable(SomeData) values('someData2')
insert into #TempTable(SomeData) values('someData3')
insert into #TempTable(SomeData) values('someData4')

select * from #TempTable

--Records to be deleted
SELECT ID
FROM #TempTable
WHERE ID NOT IN
(
    select MAX(ID)
    from #TempTable
    group by SomeData
)

--Delete them
DELETE
FROM #TempTable
WHERE ID NOT IN
(
    select MAX(ID)
    from #TempTable
    group by SomeData
)

--Final Result Set
select * from #TempTable

drop table #TempTable;

CTE, :

WITH UniqueRecords AS
(
    select MAX(ID) AS ID
    from #TempTable
    group by SomeData
)
DELETE A
FROM #TempTable A
    LEFT outer join UniqueRecords B on
        A.ID = B.ID
WHERE B.ID IS NULL
+6

,
drop source table, .

#TempTable, SrcTable, )

create table SrcTable
(
    ID int identity(1,1) not null primary key,
    SomeData varchar(100) not null
)

insert into SrcTable(SomeData) values('someData1')
insert into SrcTable(SomeData) values('someData1')
insert into SrcTable(SomeData) values('someData2')
insert into SrcTable(SomeData) values('someData2')
insert into SrcTable(SomeData) values('someData2')
insert into SrcTable(SomeData) values('someData3')
insert into SrcTable(SomeData) values('someData4')


-- cloning "unique" part
SELECT * INTO TempTable 
FROM SrcTable --original table
WHERE id IN  
(SELECT MAX(id) AS ID
FROM SrcTable
GROUP BY SomeData);
GO;

DROP TABLE SrcTable
GO;

sys.sp_rename 'TempTable', 'SrcTable'
+2

ROW_NUMBER()

;WITH [CTE_DUPLICATES] AS 
(
SELECT RN = ROW_NUMBER() OVER (PARTITION BY SomeData ORDER BY SomeData)
FROM #TempTable
) 
DELETE FROM [CTE_DUPLICATES] WHERE RN > 1
+1
source 
SET ROWCOUNT 1    
DELETE Table    
FROM Table a    
WHERE (SELECT COUNT(*) FROM Table b WHERE b.Code = a.Code ) > 1    
WHILE @@rowcount > 0    
  DELETE Table    
  FROM Table a    
  WHERE (SELECT COUNT(*) FROM Table b WHERE b.Code = a.Code ) > 1    
SET ROWCOUNT 0

this will delete all duplicate rows, but you can add attributes if you want to compare them.

0
source

More articles:


All Articles