我想在我的数据库中更新一行数据。问题是,由于我的错误,我有两行完全相同的数据。如何只对其中一行运行更新操作?
在SQL Server 2005+中,您可以使用
UPDATE TOP (1) ....
相比于使用SET ROWCOUNT
,这种方法的优点在于任何触发器不会受到ROWCOUNT
限制的影响,这几乎肯定是一件好事。
通常表格都有唯一的ID。你应该按照此ID进行筛选。
例如:
UPDATE YourTable
SET YourColumnToUpdate = 'your_value'
WHERE YourUniqueColumn = @Id
如果您的表没有唯一标识符,请考虑添加一个:一个带有Primary Key
和Identity
的integer
列。
使用 SET ROWCOUNT;
SET ROWCOUNT 1; UPDATE Production.ProductInventory SET Quantity = 400 WHERE Quantity < 300; GO
-- 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
UPDATE TOP (1) TABLE_NAME SET COLUMN_NAME = 'TOP 1'
将表名为TABLE_NAME的第一行记录的COLUMN_NAME列更新为'TOP 1'。 - Manivannan Nagarajan