Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2003
    Location
    SoCal
    Posts
    721

    Unanswered: Update records on a Select

    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!

  2. #2
    Join Date
    Sep 2003
    Posts
    2

    Re: Update records on a Select

    I think this could help you:


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



  3. #3
    Join Date
    Jul 2003
    Location
    SoCal
    Posts
    721
    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...
    That which does not kill me postpones the inevitable.

  4. #4
    Join Date
    Jul 2003
    Location
    SoCal
    Posts
    721
    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..
    That which does not kill me postpones the inevitable.

  5. #5
    Join Date
    Jul 2003
    Location
    SoCal
    Posts
    721
    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...
    That which does not kill me postpones the inevitable.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •