update using subselect

ScientificLee · #1 (**permalink**) 09-25-03, 08:23

Hello,

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.

ScientificLee · #2 (**permalink**) 09-25-03, 08:28

Code:

UPDATE executions
 SET orderID =
  (SELECT O.orderID
   FROM orders AS O
   WHERE
    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

ScientificLee · #3 (**permalink**) 09-25-03, 14:24

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?

Internet Services

Web Development

Digital Marketing

Consumer Tech