UPDATE with JOIN in ORACLE

Suppose we want to update in our ORACLE database the costs fields of the fact table FAC_TABLE with the unit cost of our table COSTS.

We can do this in two ways:

1. (Slow, but valid for a few data or to sporadic uses)

update FAC_TABLE ft  set UNIT_COST = (select distinct UNIT_COST from COSTS ct  where (ft.id_article = ct.id_article);

2. (The best way is this, and the performance is ideal if you have constraints)

UPDATE ( SELECT ft.UNIT_COST AS old_cost,  ct.UNIT_COST AS new_cost FROM FAC_TABLE ft  INNER JOIN COSTS ct ON ft.id_article ct = ct.id_article) )  SET old_cost = new_cost;

To the proper functionality of this second option you need a UNIQUE or PRIMARY KEY constraint on ct.id_articulo. 
If you don't have this constraint, you can use the hint / * + BYPASS_UJVC * / after the word UPDATE (Bypass update join view constraint).

The performance increase if we have the constraint but even without it, the second option should run quite faster than the first option.

I hope this helps.

Hector Minguet.