Updating multiple fields with values from another table

While carrying out day-to-day support for PeopleSoft systems, we often encounter cases where fields in a record must be updated with values from another table.

In this post, we will look at two ways in which this can be done in Oracle.

In both these cases, we have two tables that have id as the primary key.

Our aim is to update the values of NAME and DOB (date of birth) in the STAFF with values from the EXT_APP table.

Using a Sub-query

In this method, we select the NAME and DOB from EXT_APP in a subquery and use these to set the NAME and DOB fields in the STAFF table.

We also use an EXISTS clause so that only the IDs that have a corresponding row in EXT_APP are affected by this update.
This will prevent the operation from trying to set NULL values in the STAFF table in cases where no corresponding rows are found in EXT_APP.

UPDATE STAFF A
SET
  (A.NAME, A.DOB ) =
  (SELECT 
   B.NAME, B.DOB 
   FROM EXT_APP B 
     WHERE B.ID = A.ID)
WHERE EXISTS (
    SELECT 1
      FROM EXT_APP C
     WHERE A.ID = C.ID);

Using Merge

Lately, I’ve been using MERGE to carry this out as I’ve noticed that it runs faster when working with larger datasets.

We use the USING clause to specify the Source of data.
In our case, it comes from the EXT_APP table.

The ON is used to specify the condition on which the Update is carried out.

When we have a MATCH, the UPDATE of the fields A.NAME and A.DOB are performed.

MERGE INTO 
STAFF A
 USING
(SELECT * FROM EXT_APP) B
 ON (A.ID = B.ID)
WHEN MATCHED 
 THEN UPDATE 
 SET
   A.NAME = B.NAME,
   A.DOB = B.DOB;

Merge is very powerful and can be used in advanced operations.
But in this post, we’ve looked only at the UPDATE operation.

For a complete reference of what this can be used for, refer to the Oracle documentation.

Hope these come in handy for you.