有没有办法同时 SELECT 和 UPDATE 行? [英] Is there a way to SELECT and UPDATE rows at the same time?

问题描述

我想根据一个简单的条件更新一组行并获取已更改的 PK 列表.我以为我可以做这样的事情，但我担心可能出现的并发问题:

I'd like to update a set of rows based on a simple criteria and get the list of PKs that were changed. I thought I could just do something like this but am worried about possible concurrency problems:

SELECT Id FROM Table1 WHERE AlertDate IS NULL;
UPDATE Table1 SET AlertDate = getutcdate() WHERE AlertDate IS NULL;

如果将其封装在事务中，是否会出现并发问题?或者有没有更好的方法来做到这一点?

If that is wrapped in a transaction are there any concurrency issues that can occur? Or is there a better way to do this?

推荐答案

考虑查看 OUTPUT 子句:

USE AdventureWorks2012;  
GO  

DECLARE @MyTableVar table(  
    EmpID int NOT NULL,  
    OldVacationHours int,  
    NewVacationHours int,  
    ModifiedDate datetime);  

UPDATE TOP (10) HumanResources.Employee  
SET VacationHours = VacationHours * 1.25,  
    ModifiedDate = GETDATE()   
OUTPUT inserted.BusinessEntityID,  
       deleted.VacationHours,  
       inserted.VacationHours,  
       inserted.ModifiedDate  
INTO @MyTableVar;  

--Display the result set of the table variable.  
SELECT EmpID, OldVacationHours, NewVacationHours, ModifiedDate  
FROM @MyTableVar;  
GO  
--Display the result set of the table.  
SELECT TOP (10) BusinessEntityID, VacationHours, ModifiedDate  
FROM HumanResources.Employee;  
GO 

这篇关于有没有办法同时 SELECT 和 UPDATE 行?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！