Deleting a duplicate record

Recently a friend of mine asked me about deleting a duplicate record from a table without deleting both records. Having been in a situation I drafted up a sample script and emailed it to him.

DECLARE @tbl1 TABLE
(
   id int,
   color varchar(50)
)
INSERT INTO @tbl1 (id, color) 
VALUES (1,'blue')
,(1,'blue')
,(2,'red')
,(3,'green')
,(4,'yellow')
,(5,'orange');

First off let’s see all the rows in the table.

SELECT * FROM @tbl1;

00.allrecords

Now let’s list just the duplicate records

SELECT
   color
   ,COUNT(color)
FROM @tbl1
GROUP BY color
HAVING (COUNT(color) > 1);

01.listduplicaterecords

Now let’s look closer at these duplicate records to see if there is any uniqueness

SELECT * FROM @tbl1 WHERE (color = 'blue');

03

As you can see there is nothing unique about either of the records. So how can you delete just one as opposed to deleting both? You could try the following but that is similar to the query above and it will target both records.

DELETE FROM @tbl1 WHERE (color = 'blue');

Well the quickest way to accomplish would be to do the following and use the SET ROWCOUNT statement.

SET ROWCOUNT 1;
DELETE FROM @tbl1 WHERE (color = 'blue');

--REVERT ROWCOUNT
SET ROWCOUNT 0;

-- LIST ALL ROWS
SELECT * FROM @tbl1;

02.deletedrecord