I am working on an update query where I need to update a column LOB of table SID from two other tables.

Below are the three tables involved:

My requirement is below:

1. Match acc_grid of SID with grid of DMM and update the LOB of SID.
2. For the records where no match found in DMM ( e.g. 100045) , look for this grid in Matrix. Matching condition will be GRID of Matrix and acc_grid of SID. Get the DR_GRID of that GRID in Matrix ( which will be same).
3. Find this DR_GRID in DMM and then update the SID.

How can i achieve the same in a single update query (if possible).

Output:

CodePudding user response：

Here's one option; it uses merge with union of two sets: one represents dmm table, and another is outer join of dmm and matrix. It results in all possible combinations of grid lob so all you have to do is to match sid to that union set on the grid value.

SQL> merge into sid s
  2    using (select m.grid, d.lob
  3           from dmm d join matrix m on m.dr_grid = d.grid
  4           union
  5           select d.grid, d.lob
  6           from dmm d
  7          ) x
  8    on (x.grid = s.acc_grid)
  9    when matched then update set
 10      s.lob = x.lob;

3 rows merged.

SQL> select * From sid;

  ACC_GRID LOB
---------- ---
    100015 G
    100045 G
    234546 G

SQL>