Oracle Tips - UPDATE in Case of Referencing Another Table

  

Scenario

Updating a table by referencing another table is very common in either OLTP or OLAP systems. For example, table C is populated from a master table M. When the data in M is updated, the data in C will be modified accordingly. From the perspective of data integrity, the master table holds the valid data, so when we make modifications to records in C, we'll need to ensure the records are still valid by referencing them back to table M through checking the data keys. 

Example

In this example,  POS_ALLPROD refers to the master table, and POS_ALLPROD_CLONED stands for the populated table. 

Using Data From Another Table

This can be done by simply putting the values on the right side of the equal operator selected from the other table. 

UPDATE pos_allprod_cloned c
    SET (c.position, c.region, c.valuedate) =
                     (SELECT m.position, m.region, m.valuedate 
                      FROM pos_allprod m 
                      WHERE m.customer_id = c.customer_id)
WHERE c.product = 'BOND' 

;

 

SemiJoin To Check Data Integrity
Basically the  master table holds the data effective up to date. If we want to check the validity of the
 record we are going to update, we can reference them back to the master table through semijoin, as 
stated below. 

UPDATE pos_allprod_cloned c
    SET c.valuedate = 20230616    
WHERE EXISTS 
      (SELECT 1
       FROM pos_allprod m 
       WHERE m.customer_id = c.customer_id)

;

Combine Them Together
How about putting them together, saying, using data in the master table for update and check data 
integrity at the same time? Surely it is doable.

UPDATE pos_allprod_cloned c
    SET (c.position, c.region, c.valuedate) =
                     (SELECT m.position, m.region, m.valuedate 
                      FROM pos_allprod m 
                      WHERE m.customer_id = c.customer_id)
WHERE EXISTS 
      (SELECT 1
       FROM pos_allprod m 
       WHERE m.customer_id = c.customer_id)

;

Another Way To Do This

We can also do this by joining the tables first, then carry out the updates. 

UPDATE 
    (SELECT 
        c.position, c.region, c.valuedate,
        m.position mposition, m.region mregion, m.valuedate mvaluedate
    FROM pos_allprod_cloned c
    JOIN pos_allprod m
    ON m.customer_id = c.customer_id
    ) u
SET u.position = u.mposition,
    u.region = u.mregion,
    u.valuedate = u.mvaluedate

;

This works like we perform updates on an updatable view. So there are constraints for this operation 
like the underlying tables must be key-preserved.

Reference:

Update statement with inner join on Oracle