使用T-SQL更新单个行

Question

我想在我的数据库中更新一行数据。问题是，由于我的错误，我有两行完全相同的数据。如何只对其中一行运行更新操作？

- dan_vitch

Answer 1

在SQL Server 2005+中，您可以使用

UPDATE TOP (1) ....

相比于使用SET ROWCOUNT，这种方法的优点在于任何触发器不会受到ROWCOUNT限制的影响，这几乎肯定是一件好事。

- spender

---

UPDATE TOP (1) TABLE_NAME SET COLUMN_NAME = 'TOP 1' 将表名为TABLE_NAME的第一行记录的COLUMN_NAME列更新为'TOP 1'。 - Manivannan Nagarajan

Answer 2

通常表格都有唯一的ID。你应该按照此ID进行筛选。

例如：

UPDATE YourTable
SET YourColumnToUpdate = 'your_value'
WHERE YourUniqueColumn = @Id

如果您的表没有唯一标识符，请考虑添加一个：一个带有Primary Key和Identity的integer列。

- Bill Paetzke

Answer 3

我建议您返回并规范化数据库。至少添加一个自动递增的int主键列并使用该id。使用UPDATE TOP 1可能有效并直接回答了您的问题，但是数据库的非规范化是“真正”的问题。

http://en.wikipedia.org/wiki/Database_normalization

- RandyMorris

Answer 4

使用 SET ROWCOUNT;

SET ROWCOUNT 1; UPDATE Production.ProductInventory SET Quantity = 400 WHERE Quantity < 300; GO

http://msdn.microsoft.com/en-us/library/ms188774.aspx

- Ryan Anderson

---

在 SQL Server 的下一个版本中，使用 SET ROWCOUNT 不会影响 DELETE、INSERT 和 UPDATE 语句。不要在新的开发工作中使用 SET ROWCOUNT 与 DELETE、INSERT 和 UPDATE 语句，并计划修改当前使用它的应用程序。此外，对于当前使用 SET ROWCOUNT 的 DELETE、INSERT 和 UPDATE 语句，我们建议您重写它们以使用 TOP 语法。有关更多信息，请参阅 DELETE (Transact-SQL)、INSERT (Transact-SQL) 或 UPDATE (Transact-SQL)。 - Francisco Soto

非常好，Francisco。这很重要，需要知道！谢谢！ - Ryan Anderson

Answer 5

-- 01. create the table for the test-data
CREATE TABLE dbo.Orders (
    ID INTEGER
    ,Value DECIMAL(10, 2)
    ,Descr NVARCHAR(100)
    ,BookingDate DATETIME
    ,UserName NVARCHAR(100)
    ,CountMeasure INTEGER
)

-- 02. save the timestamp for the inserts
DECLARE @date AS DATETIME
SET @date = getdate()

-- 03. inserting test-data
INSERT INTO dbo.Orders VALUES (1,499.99,'Notebook',@date,'tgr',0)
INSERT INTO dbo.Orders VALUES (2,650.00,'PC',@date,'tgr',0)
INSERT INTO dbo.Orders VALUES (3,29.50,'Keyboard',@date,'tgr',0)

-- 04. adding the duplicate entry
INSERT INTO dbo.Orders VALUES (2,650.00,'PC',@date,'tgr',0)

-- 05. viewing the 4 Rows
SELECT * FROM dbo.Orders

-- 06. viewing the distinct 3 Rows
SELECT DISTINCT * FROM dbo.Orders

/* You don't want to delete the duplicate row, but you want to count 
   the distinct IDs using SUM on the CountMeasure-Column */

-- 07. alternativ solution (which may does not fit your requirements)
/* So your result shoud be the same like this */
SELECT COUNT(DISTINCT ID) as DistinctCount
FROM dbo.Orders

-- 08. Understanding the solution
/* To understand the solution we take a look on the main-part 
   We generate for each ID a Row-Number ordered by random  
   Details: https://msdn.microsoft.com/de-de/library/ms186734%28v=sql.120%29.aspx */
SELECT * , ROW_NUMBER() OVER (PARTITION BY ID ORDER BY NEWID()) AS RowNumberForEachId
FROM dbo.Orders

-- 09. The update statement
/* We use this part to update our table */
UPDATE a
SET CountMeasure = 1
FROM (-- Orders incl
      SELECT * , ROW_NUMBER() OVER (PARTITION BY ID ORDER BY NEWID()) as rowNum
      FROM dbo.Orders
) as a
WHERE rowNum = 1

-- 10. Viewing the Result 
SELECT * FROM dbo.Orders 

-- 11. Comparing Count(DISTINCT ...) with the SUM(...) alternative
SELECT COUNT(DISTINCT ID) as countDistinct, SUM(CountMeasure) as sumCountMeasure
FROM dbo.Orders

-- 12. Removing the test-table
DROP TABLE dbo.Orders

- tgr