Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2003

    Unanswered: update using subselect


    I have to update a table but using a subselect.
    I'll first try to explain it in english.

    I have an execution table which has as values executionID, symbol, volume, executionDate, orderID and side. I also have an order table which has an orderID, symbol, orderDate, side, matchStatus, and volume.

    For each record in order there is one or more records in the execution table. Think of it this way. There is an order to BUY 100 shares of XYZ. The could be 2 executions for XYZ at 50 shares.

    The execution.orderID and order.orderID is supposed to be the key that links each order with all the executions. The problem is that I have to create this relationship. I have all the information execept the orderID.

    I need the DB to go through all the records that have a orders.matchStatus NULL and executions.orderID NULL and where the sum(volume) from the execution table equals the abs(volume) from the orders table - find the match and update.

    The problem so far is that my subselect is returning too may records set.

  2. #2
    Join Date
    Sep 2003
    UPDATE executions
     SET orderID =
      (SELECT O.orderID
       FROM orders AS O
        O.symbol = executions.symbol
        AND LEFT(O.side, 1) = LEFT(executions.side, 1)
    	 AND O.orderdate = executions.executiondate AND O.matchStatus IS NULL AND executions.orderID IS NULL
        AND O.orderID in 
    		select o.orderID
    		from executions e, orders o
    		where e.symbol = o.symbol AND LEFT(o.side, 1) = LEFT(e.side, 1) AND 
    			e.orderID IS NULL and o.matchStatus is NULL AND e.executiondate = o.orderdate
    		group by o.orderid, o.volume
    		HAVING SUM(e.volume) = ABS(o.volume)
     WHERE orderID IS NULL

  3. #3
    Join Date
    Sep 2003
    OK I found the problem.
    some of the conditions are such that the subselect query will return multiple rows. This is a big no no.

    For example lets say that you bought 1000 shares of MSFT at 25 in one execution.
    But at the sametime someone else who's information is going into the DB bought 1000 shares of MSFT at 25 in one execution.

    in the query one would get back 2 two rows for the subselect because the conditions are matched. Anyway to have the update just ingnore this? Having count 1?

Posting Permissions

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