Is there a way to SELECT and UPDATE rows at the same time?

David_Thomas_Garcia_40161 0
Viewed 149439

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?

sql-server-2008 tsql sql-server sql
9 Answers

Consider looking at the OUTPUT clause:

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
user_407250

One way to handle this is to do it in a transaction, and make your SELECT query take an update lock on the rows selected until the transaction completes.

BEGIN TRAN

SELECT Id FROM Table1 WITH (UPDLOCK)
WHERE AlertDate IS NULL;

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

COMMIT TRAN

This eliminates the possibility that a concurrent client updates the rows selected in the moment between your SELECT and your UPDATE.

When you commit the transaction, the update locks will be released.

Another way to handle this is to declare a cursor for your SELECT with the FOR UPDATE option. Then UPDATE WHERE CURRENT OF CURSOR. The following is not tested, but should give you the basic idea:

DECLARE cur1 CURSOR FOR
  SELECT AlertDate FROM Table1 
  WHERE AlertDate IS NULL
  FOR UPDATE;

DECLARE @UpdateTime DATETIME

SET @UpdateTime = GETUTCDATE()

OPEN cur1;

FETCH NEXT FROM cur1;

WHILE @@FETCH_STATUS = 0
BEGIN

  UPDATE Table1 
  SET AlertDate = @UpdateTime  --set value
  WHERE CURRENT OF cur1;

  FETCH NEXT FROM cur1;
  
END
Bill_Karwin_208600

It'd be easier to do your UPDATE first and then run 'SELECT ID FROM INSERTED'.

Take a look at SQL Tips for more info and examples.

Kevin_Fairchild_37430

Perhaps something more like this?

declare @UpdateTime datetime

set @UpdateTime = getutcdate()

update Table1 set AlertDate = @UpdateTime where AlertDate is null

select ID from Table1 where AlertDate = @UpdateTime
user_164730

Edit: my bad, you wanted the select to show results after the update, not update from a select.

Have you tried a sub-select?

update mytable set mydate = sysdate 
where mydate in (select mydate from mytable where mydate is null);
Admin1

if it's inside the transaction, the database locking system will take care of concurrency issues. of course, if you use one (the mssql default is that it uses lock, so it states if you don't override that)

zappan_47230
Admin1
Related