SQL Server: Delete/Update duplicate records

Problem:

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.

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:

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

Analysis:

According to MSDNSET 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.

[+] ADDED LATER [+]
[Thanks Manas Ranjan Dash for bringing this to notice]
As the MSDN link says “Using SET ROWCOUNT will not affect DELETE, INSERT, and UPDATE statements in the next release of SQL Server”, lets try some other alternative solutions.
Solution 2:
Using TOP –>

DELETE TOP (1) FROM Demo WHERE Id=3 AND Name='Test3'

This query will delete 1 record and you can change it as required.
Solution 3:
Using Common Table Expression & Ranking Functions –>

;WITH CTE(Id,Name,SlNo)
AS
(
	SELECT Id,Name,ROW_NUMBER() OVER(ORDER BY Id) AS SlNo
	FROM Demo
)
DELETE FROM CTE WHERE SlNo > 1 AND Id=3 AND Name='Test3'

The above script will leave only a single record with Id=3 and Name=’Test3′.

Hopefully this article may be useful to you. Please put your comments and suggestions.

Thanks !!!

Advertisements

7 thoughts on “SQL Server: Delete/Update duplicate records

    • Sir, thanks for your valuable feedback. I haven’t noticed the remarks section.I’ll try to update the post with a proper solution as suggested by you, very soon.

  1. Hi sir,
    how to update multiple records of multiple rows of a table using stored procedure at a time.Ex: id name
    ————
    1 raju
    1 raju
    2 seenu
    3 manu
    3 manu
    4 sam

  2. Joins———-
    EMP–Table
    eid ename esal deptno
    ——————————
    15 raju 1000.00 60
    10 jan 40000.00 10
    11 rqm 60000.00 10
    13 sam 50000.00 20
    100 NULL 30000.00 20

    Dept —Table
    Deptno Deptname
    ————-
    10 hr
    20 production
    30 finance

    Inner Join
    select emp.eid,emp.ename,dept.deptname from dept inner join emp on (emp.edeptno=dept.deptno)
    eid ename deptname
    ——————–
    10 jan hr
    11 rqm hr
    13 sam production
    100 NULL production

    Left outer join
    select emp.eid,emp.ename,dept.deptname from dept left outer join emp on (emp.edeptno= dept.deptno)
    eid ename deptname
    ——————–
    10 jan hr
    11 rqm hr
    13 sam production
    100 NULL production
    NULL NULL finance

    Right outer join
    select emp.eid,emp.ename,dept.deptname from dept right outer join emp on (emp.edeptno= dept.deptno)
    eid ename deptname
    ——————–
    15 raju NULL
    10 jan hr
    11 rqm hr
    13 sam production
    100 NULL production

    Full outer join
    select emp.eid,emp.ename,dept.deptname from dept full outer join emp on (emp.edeptno= dept.deptno)
    eid ename deptname
    ——————–
    10 jan hr
    11 rqm hr
    13 sam production
    100 NULL production
    NULL NULL finance
    15 raju NULL

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