1

I have a table that has a column processed it is value is zero for failure and 1 for success. I want to add a third temporary case, its value is 2.

my previous query is:

string query = "SELECT * FROM dbo.tableName WHERE processed = @processed";

I would like to do update for the rows that this selects makes. I can do that from .net of course, but I want to know if there is one query that can select the rows and update the process column at the same time.

i am working with sql server 2008

That question states a very old solution, which is the output, and I couldn't discover it my self.

James Z
  • 12,209
  • 10
  • 24
  • 44
Marco Dinatsoli
  • 10,322
  • 37
  • 139
  • 253
  • 4
    [There](http://stackoverflow.com/questions/1610509/getting-the-id-of-a-row-i-updated-in-sql-server) [are](http://stackoverflow.com/questions/6984631/sql-server-stored-procedure-that-returns-updated-records) [so](http://stackoverflow.com/questions/700786/sql-update-a-row-and-returning-a-column-value-with-1-query) [many](http://stackoverflow.com/questions/1367018/get-updated-row) [duplicates](http://stackoverflow.com/questions/12414104/how-to-use-output-clause-of-sql-server-for-update) – Yosi Dahari Sep 21 '15 at 20:59

2 Answers2

4

You can use OUTPUT clause:

UPDATE dbo.tableName 
SET processed = 2 --or whatever you want..
OUTPUT inserted.*
WHERE processed = @processed

From MSDN (Link above):

Returns information from, or expressions based on, each row affected by an INSERT, UPDATE, DELETE, or MERGE statement. These results can be returned to the processing application

Yosi Dahari
  • 6,794
  • 5
  • 24
  • 44
0

I assume that you're wanting to do some kind of atomic record-lock on the rows selected. Not sure about T-SQL, but in Oracle you could do this with an UPDATE table SET PROCESSED = 2 WHERE conditions RETURNING primary_key BULK COLLECT INTO array. Then do a select * for the rows whose keys were stored in the array.

I think the T-SQL syntax would be something like UPDATE table SET PROCESSED = 2 OUTPUT primary_key INTO temp_table WHERE conditions.

Paul Kienitz
  • 878
  • 6
  • 25
  • so you are suggesting to change my enterprise's database to use Oracle? – Marco Dinatsoli Sep 21 '15 at 20:42
  • seriously read my complain on the meta http://meta.stackoverflow.com/questions/305838/how-can-i-avoid-duplicates-that-really-arent typical situation – Marco Dinatsoli Sep 21 '15 at 20:43
  • I'm just suggesting you could try that kind of approach, translating it into T-SQL, if something equivalent to "returning / bulk collect into" is available. – Paul Kienitz Sep 21 '15 at 23:09