Correlated Update : A tricky Situation

Hi Folks,

Long time no see, any post.
Last day I saw a bewildering issue. It may not strike some of you if you already know it.
But i thought this should be shared.

In a correlated update :
eg:
UPDATE MASTER_ORDERS X
SET QTY=(SELECT Y.QTY
FROM ORDERS Y
WHERE X.ORDER_NUM = Y.ORDER_NUM);


We know one thing ; that is it will scan the whole table and consider each row for updating.
But in this case if the inner query returns not result, still the update will complete. It will update even the row for which the inner query did not give any data with NULL. Some kind of force update .Hence resulting in loss of data.
Tom Kyte says this not the right way to do such an update.

To avoid this there are two methods:

- Use an updatable join (updatable view), if the row to be updated is key preserved (i.e; only one row is fetched for the join condition).

- Use an exists clause in the outer where condition, so that only records with change are picked for update. It also makes the query faster as the update is done on a smaller subset.

UPDATE MASTER_ORDERS X
SET QTY=(SELECT Y.QTY
FROM ORDERS Y
WHERE X.ORDER_NUM = Y.ORDER_NUM)
WHERE EXISTS (SELECT 1
FROM ORDERS Y
WHERE X.ORDER_NUM = Y.ORDER_NUM);