How to Improve Updating a Table's Fields That are Referenced in Subqueries

Question

I have a table that is truncated, then loaded with several hundreds of thousands of records each time by a process. The next process needs to update several columns for each record (that don't have values) by basically looking up values from various other tables while utilizing some of the existing updating table's own columns as the reference (in example below, this could be TABLE_A.P1 and TABLE_A.P2), e.g.:

UPDATE TABLE_A A SET
  A.COL1 = (
    SELECT B.COL_BX
    FROM TABLE_B B
    WHERE B.P1 = A.P1 -- Reference back to the updating table's column
  ),
  A.COL2 = (
    SELECT D.COL_DY
    FROM TABLE_C C INNER JOIN TABLE_D D
      ON C.H1 = D.H1
    WHERE C.P1 = A.P1 -- Reference back to the updating table's column
                      -- Would this act as a join between A and C (not implementation-wise but rather functionality)? 
  ),
  A.COL3 = (
    SELECT G.COL_GZ
    FROM TABLE_E E INNER JOIN TABLE_F F
        ON E.N1 = F.N1
      OUTER JOIN TABLE_G G
        ON G.M1 = F.M1
    WHERE E.P1 = A.P1
      AND E.P2 = A.P2
      -- AND (bunch of other predicate for G columns)
  ); -- There are more columns to be updated but omitted for brevity

When I run such update, the session times out. My question is, is there a better way to write this update given the above scenario?

Answer 1

You might find that separate update statements are faster:

UPDATE TABLE_A A
  SET A.COL1 = (SELECT B.COL_BX
                  FROM TABLE_B B
                  WHERE B.P1 = A.P1);

UPDATE TABLE_A A
  SET A.COL2 = (SELECT D.COL_DY
                  FROM TABLE_C C
                  INNER JOIN TABLE_D D
                    ON C.H1 = D.H1
                  WHERE C.P1 = A.P1);

UPDATE TABLE_A A
  SET A.COL3 = (SELECT G.COL_GZ
                  FROM TABLE_E E
                  INNER JOIN TABLE_F F
                    ON E.N1 = F.N1
                  LEFT OUTER JOIN TABLE_G G
                    ON G.M1 = F.M1
                  WHERE E.P1 = A.P1 AND
                        E.P2 = A.P2);

Best of luck.