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;
Now let’s list just the duplicate records
SELECT color ,COUNT(color) FROM @tbl1 GROUP BY color HAVING (COUNT(color) > 1);
Now let’s look closer at these duplicate records to see if there is any uniqueness
SELECT * FROM @tbl1 WHERE (color = 'blue');
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;