sqlt-sqltransactionspetapoco

How to select and update selected rows in a single sql query?

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 !)

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