oracle update fields using joining other table

The short URL for this FAQ is http://tinyurl.com/2vphxg
Here are four examples of updating multiple columns with a single statement. The DML options are UPDATE, 10gr2 MERGE, 9i MERGE. Another option, if you can join on a database-enforced unique constraint (for example, a primary key), is "updating a join".
Set up the example: create table tgt ( id number /*primary key*/, x number, y number )
/
insert into tgt ( id, x, y )
select level, level, level from dual connect by level < 4
/
create table src ( id number /*primary key*/, x number, y number )
/
insert into src ( id, x, y )
select level+1, level*5, level*5 from dual connect by level < 3
/
commit
/
select * from src;
ID X Y
---------- ---------- ----------
2 5 5
3 10 10
select * from tgt;
ID X Y
---------- ---------- ----------
1 1 1
2 2 2
3 3 3
The first example is an UPDATE statment: UPDATE tgt
SET ( tgt.x, tgt.y ) =
( SELECT src.x, src.y
FROM src
WHERE src.id = tgt.id
)
WHERE EXISTS
( SELECT src.x, src.y
FROM src
WHERE src.id = tgt.id
)
/
2 rows updated.
select * from tgt;
ID X Y
---------- ---------- ----------
1 1 1
2 5 5
3 10 10
The second example uses a 10gR2 MERGE statement: MERGE into tgt /* Oracle 10gR2 doesn't require WHEN NOT MATCHED */
USING src
ON ( src.id = tgt.id )
WHEN MATCHED THEN UPDATE SET
tgt.x = src.x
, tgt.y = src.y
/
2 rows updated.
select * from tgt;
ID X Y
---------- ---------- ----------
1 1 1
2 5 5
3 10 10
The third example using a 9i MERGE, which requires (even if it doesn't use) a WHEN NOT MATCHED clause: MERGE into tgt /* Oracle 9i requires WHEN NOT MATCHED */
USING
( SELECT src.x, src.y, src.id
FROM src INNER JOIN tgt
ON ( src.id = tgt.id )
) src
ON ( src.id = tgt.id )
WHEN MATCHED THEN UPDATE SET
tgt.x = src.x
, tgt.y = src.y
WHEN NOT MATCHED -- NEVER INVOKED
THEN INSERT -- NEVER INVOKED
( tgt.id ) -- NEVER INVOKED
VALUES -- NEVER INVOKED
( src.id ) -- NEVER INVOKED
/
2 rows updated.
select * from tgt;
ID X Y
---------- ---------- ----------
1 1 1
2 5 5
3 10 10
The previous examples succeeded despite a lack of database-enforced uniqueness constraints. SRC.ID had to be unique in practice, otherwise Oracle will throw the error: ORA-30926: unable to get a stable set of rows in the source tables
If there are multiple subquery results per updated row, the DBMS doesn't know which source row to use for the update. However, uniqueness didn't have to be declared in DDL to the DBMS.
The fourth example, "updating a join", requires database-enforced uniqueness on SRC.ID. In other words, SRC must be key preserved not just in practice, but enforced design. Updating a join doesn't check for a "stable set of rows" on-the-fly, it demands a "stable set of rows" be database-enforced before hand.
Here the UPDATE is without a unique constraint (like the previous three examples). It fails: UPDATE
( SELECT src.x src_x, src.y src_y
, tgt.x tgt_x, tgt.y tgt_y
FROM src INNER JOIN tgt
ON ( src.id = tgt.id )
)
SET tgt_x = src_x
, tgt_y = src_y
/
SET tgt_x = src_x
*
ERROR at line 7:
ORA-01779: cannot modify a column which maps to a non key-preserved table
So "key preserve" the join by putting a primary key on src.ID. alter table src add primary key (id)
/
Table altered.
Then updating a join will succeed: UPDATE
( SELECT src.x src_x, src.y src_y
, tgt.x tgt_x, tgt.y tgt_y
FROM src INNER JOIN tgt
ON ( src.id = tgt.id )
)
SET tgt_x = src_x
, tgt_y = src_y
/
2 rows updated.
select * from tgt;
ID X Y
---------- ---------- ----------
1 1 1
2 5 5
3 10 10
Note that the use of column aliases. This ensured unique column