How to select and update selected rows in a single sql query?
petapoco, sql, t-sql, transactions
Solution
As long as you are using SQL Server 2005 or later you can make use of `OUTPUT` in a single query.
UPDATE Car
SET sent_to_server = 1
OUTPUT Inserted.id, Inserted.plate
WHERE sent_to_server = 0;
This will update the rows where `sent_to_server` is zero and return the modified rows. No need for a transaction.
SQL Fiddle
Problem
I have a table named `Car` ``` Table car ( id int NOT NULL, plate int NOT NULL, sent_to_server bit NOT NULL ); ``` I want to select all Cars which are not sent to server yet ``` SELECT * FROM car WHERE sent_to_server = 0; ``` Then I should update my DB ``` UPDATE car SET sent_to_server = 1 WHERE sent_to_server = 0; ``` I have multiple threads so this wont work (multiple threads are reading and writing to the database at the same time -(im using sql server)) How can I execute 2 queries in One Query ? Or is there a better solution !? note: i'm using C# with petapoco library (if it matters !)