SQL Server: Delete/Update duplicate records
My third article in Problem-Solution approach to keep it short and simple.
When you insert two records with same values for each of the columns in a table and then if you want to delete one of those two records then you may face problems like if you open the table data in SSMS and try to delete one of those rows by right clicking then it will not work. If you try to delete with a simple delete query then both the duplicate records will be deleted. You will face the same problem while updating those records also. Lets examine it using a simple example.
CREATE TABLE Demo (Id INT,Name VARCHAR(20)) INSERT INTO Demo VALUES(1,'TEST1') INSERT INTO Demo VALUES(2,'TEST2') INSERT INTO Demo VALUES(3,'TEST3') INSERT INTO Demo VALUES(3,'TEST3')
Now, try to delete/update one of the record having Id=3.
The query for Update may be like-
UPDATE Demo SET Name='3Test' WHERE Id=3
The query for Delete may be like-
DELETE Demo WHERE Id=3
Solution for Update
SET ROWCOUNT 1 UPDATE Demo SET Name='3Test' WHERE Id=3 SET ROWCOUNT 0
Solution for Delete
SET ROWCOUNT 1 DELETE FROM Demo WHERE Id=3 SET ROWCOUNT 0
According to MSDN , SET ROWCOUNT Causes SQL Server to stop processing the query after the specified number of rows are returned. So, the above query will affect only one record and this is what we needed.
Hopefully this article may be useful to you. Please put your comments and suggestions.