I have a table that stores orders. This table has a field (bProcessed - bit) that records whether the order has been processed (1) or not (0). Ideally, I'd like to update that field to a 1 when I've selected that record to be processed. If I was processing one order at a time, this would be simple, but we are batching these. So I could process 5, 10, 200 orders at one time.

My concern is that doing two statements (one to select, and another to update) may cause incongruent results. Someone could, potentially, place an order slipping between the two statements.

I do have a timestamp on the order, so one solution might be to simply limit the results to a certain date/time range, but is there a better way to do this (to select, and immediately update that selected record to change that bProcessed flag)?

Thanks!

I think this could help you:


update <table> 1 (bprocesed)
(select <idprocess>,<procesed> from table 2)
where 1.idprocess=2.idprocess

I don't need a "SET" in there?

UPDATE <table> SET bProcessed = 1 WHERE iOrderID = (SELECT <id> FROM <table> WHERE bProcessed = 0)

??

I know the above won't work.. but that's kinda what I'm trying to accomplish...

Actually, I've tried this format following the Help documents with MS SQL:

UPDATE tblOrders SET bProcessed = 1 WHERE iOrderID IN (SELECT b.iOrderID FROM tblOrders b WHERE b.bProcessed = 0)

and when run, I get an error saying:

"Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <=, >, >= or when the subquery is used as an expression."

That SQL is almost exactly from the Help docs... minor changes for table names and columns is all..

I get the same error with:

UPDATE tblOrders SET bProcessed = 1 WHERE (SELECT * FROM tblOrders WHERE bProcessed = 0) AS t1 WHERE tblOrders.iOrderID = t1.iOrderID

Now... assuming this DID work.. this wouldn't accomplish the final goal.. sure the records would be updated, but in the end, I need a recordset which contains the records that WHERE changed...