SQL Server database to delete duplicate data in the dataset

Time:2021-1-27

In SQL Server database operation, sometimes for the result set in the table, if it meets certain rules, we think it is duplicate data, and these duplicate data need to be deleted. How to delete it? In this paper, we use an example to illustrate.

Examples are as follows:

As long as the CompanyName, invoicenumber and customernumber are the same, we think they are duplicate data. The following example shows how to delete them.

declare @InvoiceListMaster table ( ID int identity primary key ,  
 
companyName Nchar(20),  
 
invoiceNumber int,  
 
CustomerNumber int,  
 
rmaNumber int )  
 
insert  @InvoiceListMaster  
 
Select n 'Huawei', 1001100200  
 
union all  
 
Select n 'Huawei', 1001100300  
 
union all  
 
Select n 'Huawei', 1001100301  
 
union all  
 
Select n 'ZTE', 1002, 200, 1     
 
union all  
 
Select n 'ZTE', 1002, 200, 2  
 
select * from @InvoiceListMaster  
 
DELETE A  
 
from (  
 
select rown = ROW_NUMBER( )over( partition by companyname,  
 
invoicenumber,  
 
customerNumber   
 
order by companyname,  
 
invoicenumber,  
 
customerNumber ),  
 
companyname,  
 
invoicenumber,  
 
customerNumber  
 
from @InvoiceListMaster )a  
 
where exists ( select 1   
 
from ( select rown = ROW_NUMBER( )over( partition by companyname,  
 
invoicenumber,  
 
customerNumber   
 
order by companyname,  
 
invoicenumber,  
 
customerNumber ),  
 
companyname,  
 
invoicenumber,  
 
customerNumber  
 
from @InvoiceListMaster ) b  
 
where b.companyName = a.companyName  
 
and b.invoiceNumber = a.invoiceNumber  
 
and b.CustomerNumber = a.CustomerNumber  
 
and a.rown > b.rown  
 
)  
 
select * from @InvoiceListMaster

The above example demonstrates the process of deleting duplicate data in the data set of SQL Server database. I hope this introduction can be helpful to you!