使用SQL Server 2008 R2删除重复ID和值ROW [英] 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:

ID    Name       Qty
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.

I have tried with following query but it removes entire(" 7   ABC   6") from table whereas I just need to remove last 2 rows only.

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

Any help is appreciated. Thanks in advance:)

推荐答案

我只能使用以下语法删除2条记录：



使用示例1;

GO

删除顶部（2）

来自Example1.dbo.RealTable

WHERE ID =''7''AND Name =''ABC''和Qty ='''6'';

GO



但是，这删除了我添加的前两个记录，而不是人们可能期望的最后两个记录。如果您可以加入另一个表并获取记录插入的某种日期时间，那么您可以命令并控制删除哪些记录。您可能还需要考虑添加某种主键或唯一索引，以便可以对行进行唯一索引。



MSDN Transact-SQL删除参考： http://msdn.microsoft.com/en-us/library/ms189835（v = sql.100）.aspx [ ^ ]

I was able to delete only 2 records using the following syntax:

USE Example1;
GO
DELETE TOP (2)
FROM Example1.dbo.RealTable
WHERE ID = ''7'' AND Name = ''ABC'' AND Qty = ''6'';
GO

However, this deleted the first two records that I had added, and not the last two as one might expect. If you can join to another table and get some sort of datetime for the record insert, then you can order by and control which records are deleted. You may also want to consider adding some sort of Primary Key or Unique Index so that the rows can be uniquely indexed.

MSDN Transact-SQL Delete Reference: http://msdn.microsoft.com/en-us/library/ms189835(v=sql.100).aspx[^]

这篇关于使用SQL Server 2008 R2删除重复ID和值ROW的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！