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

Question

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

Reedy

Why do you need that SELECT exactly? – Anomalous

i what the to send the Cars which are not sent to server yet . – Reedy

its a client server application and a server tracks multiple client apps . – Reedy

Answer 1

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

Villainy

UPDATE Car SET sent_to_server = 1 OUTPUT Inserted.* WHERE sent_to_server = 0; also works . its great !, thanks . – Reedy

@user1575632 - Yes, the * also works but there's no need to return all the table fields, you already know what the sent_to_server result will be ;) Only returning the information you need keeps the query as fast as possible. – Villainy

@Tony, is there any way to translate this to MySQL? – Blanks

@EricK Unfortunately not, as far as I'm aware, without using a transaction or the GUID batch answer to this question from Eduardo Molteni – Villainy

it's risky, because it's update the rows before sending it to the server, so if anything happens in the server u will not be able to get this data again to send it to the server. – Coalfish

@MohamedOmera True, but they were only asking how to update the database and get the result in a single query. Distributed transactions where multiple systems need to be kept in sync are beyond the scope of this question. – Villainy

Answer 2

I think you need to use transaction. Place your both queries in below code

BEGIN TRANSACTION
select * from car where sent_to_server = 0;
 update car set sent_to_server = 1 where  sent_to_server = 0;
COMMIT TRANSACTION

By default tansaction will lock the table to read for other connection, so other thread will not be able to read the result untill the transaction is not commited.

Judaic

it did not work with my library(petapoco in C#) . but it helped . petapoco supports transactions . – Reedy

Does TRANSACTION lock without a TABLOCKX on the select? – Castoff

Answer 3

Another idea to @Tony's answer, it's to change the sent_to_server column type to a Guid, and put there the processed batch number.

Example (pseudocode):

var batch = new Guid();
update car set sent_to_server = @batch where sent_to_server is null;    
select * from car where sent_to_server = @batch;

Descombes