Description of duplicate data instances in a dataset deleted from the SQL Server database, and description of server instances

Description of duplicate data instances in a dataset deleted from the SQL Server database, and description of server instances

In SQL Server database operations, when the result set in the table meets certain rules, we think it is duplicated data, and the duplicated data needs to be deleted. How to delete it? This article uses an example to describe.

Example:

As long as companyName, invoiceNumber, and mermernumber are all 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 ', 1001,100,200 union all select N 'huawei ', 1001,100,300 union all select N 'huawei', 1001,100,301 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 =. companyName and B. invoiceNumber =. invoiceNumber and B. customerNumber =. customerNumber and. rown> B. rown) select * from @ InvoiceListMaster

The preceding example demonstrates the process of deleting duplicate data in a dataset in the SQL Server database. We hope this introduction will be helpful to you!