Simultaneous Select/Update in one SP

[benjatado]

Hi,

I have a SP that I am trying to achieve simultaneous select and update of the same (Top 30) IDs within a table.

SELECT TOP 30  [ID]
FROM   Incoming
WHERE  Processing = 'N' 

UPDATE Incoming set Processing = 'Y' 
where Incoming.[ID] in (SELECT TOP 30  [ID]
FROM   Incoming
WHERE  Processing = 'N')

The question is, will this update return the same IDs as the first select statement?

Is there a way to assign a variable within the first select statement and then pass those values to the UPDATE?

Thanks,

-b

 

[markros]

What SQL Server version you're using? If it's 2005 or up, look at new OUTPUT clause for Insert/Update/Delete

http://www.kodyaz.com/articles/outp...ith-insert-update-delete-sql-server-2005.aspx

 

[benjatado]

2000 ?

 

[markros]

How are you using TOP 30 without ORDER BY ?

I believe it would be the same, but you can create a temp table to be sure.

 

[snitin78]

You should definitely have the same order by clause in your select statements. That would ensure that the same 30 Ids are returned every time. You also don't need the first select, update...select should work by itself.

 

[SQLSister]

Better yet, put the thirty rows in a temp table or table variable and join to that. Then you know you have the same thirty rows. Otherwise concurrent changes by other users could change the thirty selected even with the order by.

"NOTHING is more important in a database than integrity." ESquared

 

[markros]

Good point - perhaps this thing should be wrapped in transaction?