CODEWITHSUNDEEP
sqlsql-server-2008-r2duplicates
Joy1979
Joy1979

Reputation: 609

Delete duplicate id and Value ROW using SQL Server 2008 R2

In SQL Server 2008 R2 I added two duplicate ID and record in my table. When I try to delete one of the last two records I receive the following error.

The row values updated or deleted either do not make the row unique or they alter multiple rows.

The data is:

7   ABC         6
7   ABC         6
7   ABC         6
8   XYZ         1
8   XYZ         1
8   XYZ         4
7   ABC         6
7   ABC         6

I need to delete last two records:

7   ABC         6
7   ABC         6

I have been trying to delete last 2 record using the feature "Edit the Top 200 rows" to delete this duplicate id but get the error above.

Any help is appreciated. Thanks in advance:)

Upvotes: 1

Views: 1480

Answers (2)

JMoney
JMoney

Reputation: 321

This is an outline of code I use to delete dups in tables that may have many dups.

/* I always put the rollback and commit up here in comments until I am sure I have 
   done what I wanted. */
BEGIN tran Jim1 -- rollback tran Jim1 -- Commit tran Jim1; DROP table PnLTest.dbo.What_Jim_Deleted

/* This creates a table to put the deleted rows in just in case I'm really screwed up */
SELECT top 1 *, NULL dupflag 
  INTO jt1.dbo.What_Jim_Deleted --DROP TABLE jt1.dbo.What_Jim_Deleted
  FROM jt1.dbo.tab1;
/* This removes the row without removing the table */
TRUNCATE TABLE jt1.dbo.What_Jim_Deleted;

/* the cte assigns a row number to each unique security for each day, dups will have a
   rownumber > 1.  The fields in the partition by are from the composite key for the 
   table (if one exists.  These are the queries that I ran to show them as dups

SELECT compkey1, compkey2, compkey3, compkey4, COUNT(*) 
  FROM jt1.dbo.tab1
  GROUP BY compkey1, compkey2, compkey3, compkey4
  HAVING COUNT(*) > 1
  ORDER BY 1 DESC


*/
with getthedups as
  (SELECT *,
       ROW_NUMBER() OVER 
         (partition by compkey1,compkey2, compkey3, compkey4 
                          ORDER BY Timestamp desc) dupflag /*This can be anything that gives some order to the rows (even if order doesn't matter) */
     FROM jt1.dbo.tab1)
/* This delete is deleting from the cte which cascades to the underlying table 
   The Where is part of the Delete (even though it comes after the OUTPUT.  The
   OUTPUT takes all of the DELETED row and inserts them into the "oh shit" table,
   just in case.*/
DELETE 
  FROM getthedups 
  OUTPUT DELETED.* INTO jti.dbo.What_Jim_Deleted
  WHERE dupflag > 1

--Check the resulting tables here to ensure that you did what you think you did

/* If all has gone well then commit the tran and drop the "oh shit" table, or let it 
   hang around for a while. */

Upvotes: 0

RichardTheKiwi
RichardTheKiwi

Reputation: 107736

Since you have given no clue whatsoever that there are other columns in the table, assuming your data is in 3 columns A,B,C, you can delete 2 rows using:

;with t as (
    select top(2) *
      from tbl
     where A = 7 and B = 'ABC' and C = 6
)
DELETE t;

This will arbitrarily match two rows based on the conditions, and delete them.

Upvotes: 1

Related Questions