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.
SET 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
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
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?