SELECT *
FROM t11_executed_orders t11
WHERE t11.t11_symbol IN
(SELECT aa.t11_symbol
FROM t11_executed_orders aa
WHERE aa.t11_value = (SELECT bb.t11_price * bb.t11_filled_volume
FROM t11_executed_orders bb
WHERE bb.t11_exec_id = '123'));
the inner most query will executed first and then the nest subquery and after that the main query.
Correlated - The main query executed first and the subquery is executed against each and every row returned by the main query.
UPDATE t11_executed_orders t11
SET t11.t11_value =
(SELECT a.t11_price * a.t11_filled_volume
FROM t11_executed_orders a
WHERE t11.t11_exec_id = a.t11_exec_id
AND t11.t11_exectime_int = a.t11_exectime_int
AND t11.t11_side = a.t11_side
AND t11.t11_exchange = a.t11_exchange)
WHERE t11.t11_value <>
(SELECT a.t11_price * a.t11_filled_volume
FROM t11_executed_orders a
WHERE t11.t11_exec_id = a.t11_exec_id
AND t11.t11_exectime_int = a.t11_exectime_int
AND t11.t11_side = a.t11_side
AND t11.t11_exchange = a.t11_exchange);
the same update can be written in PL-SQL as well . like this.
BEGIN
FOR i IN (SELECT *
FROM (SELECT t11_exec_id,
t11_exchange,
t11_side,
t11_datetime,
t11_symbol,
t11_routingac,
t11_price * t11_filled_volume
AS calculated_t11_value,
t11_value
FROM mubasher_oms.t11_executed_orders)
WHERE calculated_t11_value <> t11_value)
LOOP
UPDATE mubasher_oms.t11_executed_orders
SET t11_value = i.calculated_t11_value
WHERE t11_exec_id = i.t11_exec_id
AND t11_exchange = i.t11_exchange
AND t11_side = i.t11_side;
END LOOP;
END;
for more infor
http://sql-plsql.blogspot.com/2011/09/difference-nested-correlated-subquery.html
