Multiple subquery in update or two queries (PL/SQL) ?

From: Martin T. <bilbothebagginsbab5_at_freenet.de>
Date: 12 Jul 2006 05:01:01 -0700
Message-ID: <1152705661.332636.263210@h48g2000cwc.googlegroups.com>

Hello all.

I have to do the following update:
UPDATE MEASURES
SET ORDER_ID = :p_new_order_id,

    ORDER_SORT = ORDER_SORT - (select ORDER_SORT FROM MEASURES

                     WHERE ID = :p_new_starting_measure)

WHERE ORDER_ID = :p_order_id
  AND ORDER_SORT >= (select ORDER_SORT FROM MEASURES

                     WHERE ID = :p_new_starting_measure);

As you can see, this update uses the same subquery two times. My question is both in principle and for this specific case: Would it make more sense to do it like above, or could there be valid cases where I might want to run the single-value-select query first in PL/SQL and then use it's result as a variable in the query? (Of course, if I split up the statement in two, then I may run into data integrity issues, right?)

thanks!

best,
Martin Received on Wed Jul 12 2006 - 07:01:01 CDT