How to manually delete duplicate rows with MS SQL Server

Often you just have a couple of duplicate rows that you want to delete.
It's much easier and simpler to delete them manually.
But how do you delete all the duplicates except for the one you want to keep?

To delete duplicate rows in bulk see: How to delete duplicate rows in batch

Data used

use sql911;
go

select emp_short, 
       sales_date, 
       sales_qty as 'Sold'
from sales
order by emp_short, sales_date;
go

emp_short  sales_date              Sold
---------- ----------------------- ---------------------------------------
000010     2005-04-06 15:01:01.000 4.00
000010     2005-11-06 10:01:01.000 9.00
000010     2005-12-06 11:01:01.000 8.00
000010     2005-12-06 11:01:01.000 8.00
000010     2005-12-06 11:01:01.000 8.00
000010     2006-04-06 15:01:01.000 4.00
000010     2006-05-06 16:01:01.000 3.00
000010     2006-05-06 16:01:01.000 3.00
000010     2006-07-06 19:01:01.000 1.00
000010     2007-04-06 00:00:00.000 4.00
000010     2007-04-06 00:00:00.000 4.00
000010     2007-04-06 00:00:00.000 4.00
000010     2007-04-06 00:00:00.000 4.00
000010     2007-06-06 17:01:01.000 2.00
000030     2004-02-06 13:01:01.000 6.00
000030     2005-10-06 10:01:01.000 10.00
000030     2006-01-06 12:01:01.000 7.00
000030     2006-04-06 15:01:01.000 4.00
000030     2006-04-06 15:01:01.000 4.00
000030     2006-06-06 17:01:01.000 2.00
000030     2007-05-01 00:00:00.000 5.00
000030     2007-05-01 00:00:00.000 5.00
000030     2007-05-01 00:00:00.000 5.00
000030     2007-05-01 00:00:00.000 5.00
000030     2007-05-01 00:00:00.000 5.00
000030     2007-05-01 00:00:00.000 5.00
000030     2007-05-01 00:00:00.000 5.00
000030     2007-11-06 10:01:01.000 9.00
000110     2004-03-06 14:01:01.000 5.00
000110     2005-11-06 10:01:01.000 9.00
000110     2006-02-06 13:01:01.000 6.00
000110     2006-03-06 14:01:01.000 5.00
000110     2006-05-06 16:01:01.000 3.00
000110     2006-06-06 17:01:01.000 2.00
000110     2006-07-06 19:01:01.000 1.00
000110     2006-07-06 19:01:01.000 1.00
000190     2005-03-06 14:01:01.000 5.00
000190     2005-05-06 16:01:01.000 3.00
000190     2005-12-06 11:01:01.000 8.00
000190     2006-01-06 12:01:01.000 7.00
000190     2006-01-06 12:01:01.000 7.00
000190     2006-02-06 13:01:01.000 6.00
000190     2006-05-06 16:01:01.000 3.00
000190     2006-06-06 17:01:01.000 2.00
000190     2006-06-06 17:01:01.000 2.00
000190     2006-07-06 00:00:00.000 1.00
000190     2006-07-06 19:01:01.000 1.00
000190     2007-07-06 00:00:00.000 1.00
000190     2007-07-06 00:00:00.000 1.00
000190     2007-07-06 00:00:00.000 1.00
000310     2004-10-06 10:01:01.000 10.00
000310     2005-10-06 10:01:01.000 10.00
000310     2005-10-06 10:01:01.000 10.00
000310     2005-10-06 10:01:01.000 10.00
000310     2005-11-06 10:01:01.000 9.00
000310     2005-11-06 10:01:01.000 9.00
000310     2006-01-06 12:01:01.000 7.00
000310     2006-03-06 14:01:01.000 5.00
000310     2006-07-06 19:01:01.000 1.00
000310     2006-10-06 10:01:01.000 10.00

(60 row(s) affected)

Finding the duplicate rows

  • The duplicate rows occur more than once.
  • It's kind of self-evident, but that's how we find the duplicates.

We want to find the duplicate sales. What makes a duplicate sale?

  • The same rep
  • The same date
  • The same quantity 
use sql911;
go

select emp_short, 
       sales_date, 
       sum(sales_qty) as 'Totals', 
       count(emp_short) as 'Duplicates'
from sales
group by emp_short, sales_date, sales_qty
having count(emp_short) > 1
order by emp_short;
go

emp_short  sales_date              Totals     Duplicates
---------- ----------------------- ---------- -----------
000010     2005-12-06 11:01:01.000 24.00      3
000010     2006-05-06 16:01:01.000 6.00       2
000010     2007-04-06 00:00:00.000 16.00      4
000030     2006-04-06 15:01:01.000 8.00       2
000030     2007-05-01 00:00:00.000 35.00      7
000110     2006-07-06 19:01:01.000 2.00       2
000190     2006-01-06 12:01:01.000 14.00      2
000190     2006-06-06 17:01:01.000 4.00       2
000190     2007-07-06 00:00:00.000 3.00       3
000310     2005-10-06 10:01:01.000 30.00      3
000310     2005-11-06 10:01:01.000 18.00      2

(11 row(s) affected)
  • You must group by all the columns that make the duplicate.

Deleting manually the duplicate rows

We will be dealing with employee: 000010 with sales for 2007-04-06 and a quantity sold of 4. 

select emp_short, 
       sales_date, 
       sales_qty as 'Sold'
from sales
where emp_short = '000010' and
      sales_date = '2007-04-06'
order by emp_short, sales_date;
go

emp_short  sales_date              Sold
---------- ----------------------- -------
000010     2007-04-06 00:00:00.000 4.00
000010     2007-04-06 00:00:00.000 4.00
000010     2007-04-06 00:00:00.000 4.00
000010     2007-04-06 00:00:00.000 4.00

(4 row(s) affected)

use sql911;
go

set rowcount 1
delete from sales 
where emp_short = '000010' and sales_date = '2007-04-06' and sales_qty = 4;
go

(1 row(s) affected)
  • ALWAYS, ALWAYS reselect the database before deleting. Very often, we use the same table names in different databases.
  • SET ROWCOUNT 1 is the number of: "how many rows are left". If you want to delete only 1 row at a time, you would use: SET ROWCOUNT N-1, where N is the total number of duplicates.

Verification of deleting all the duplicate rows

select emp_short, 
       sales_date, 
       sales_qty as 'Sold'
from sales
where emp_short = '000010' and
      sales_date = '2007-04-06';
go

emp_short  sales_date              Sold
---------- ----------------------- ---------------------------------------
000010     2007-04-06 00:00:00.000 4.00

(1 row(s) affected)