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

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s