SQL Remove Duplicate Records (Seven Methods)

Without further ado, please see the code:

if not object_id('Tempdb..#T') is null
 drop table #T
Go
Create table #T([ID] int,[Name] nvarchar(1)) [Memo] nvarchar(2))
Insert #T
select 1,N'A',N'A1' union all
select 2,N'A',N'A2' union all
select 3,N'A',N'A3' union all
select 4,N'B',N'B1' union all
select 5,N'B',N'B2'
Go 

--I. The record with the same Name and the smallest ID (it is recommended to use1,2,3), keep the smallest one

Method1:

delete a from #T a where exists(select 1 from #T where Name=a.Name and ID<a.ID)

Method2:

delete a from #T a left join (select min(ID)ID, Name from #T group by Name) b on a.Name=b.Name and a.ID=b.ID where b.Id is null

Method3:

delete a from #T a where ID not in (select min(ID) from #T where Name=a.Name)

Method4(Note: ID is unique when available):

delete a from #T a where ID not in(select min(ID)from #T group by Name)

Method5:

delete a from #T a where (select count(1) from #T where Name=a.Name and ID<a.ID)>0

Method6:

delete a from #T a where ID>(select top 1 ID from #T where Name=a.name order by ID)

Method7:

delete a from #T a where ID>any(select ID from #T where Name=a.Name)
select * from #T

That's all for the content of this article. I hope the content of this article can bring a certain amount of help to everyone's learning or work, and also hope to give more support to the呐喊 tutorial!

Statement: The content of this article is from the Internet, and the copyright belongs to the original author. The content is contributed and uploaded by Internet users spontaneously. This website does not own the copyright, has not been manually edited, and does not assume any relevant legal liability. If you find any content suspected of copyright infringement, please send an email to: notice#oldtoolbag.com (When reporting via email, please replace # with @) to report, and provide relevant evidence. Once verified, this site will immediately delete the content suspected of infringement.