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

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?

2 answers

  • answered 2018-07-11 04:02 Bob Jarvis

    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.

  • answered 2018-07-11 04:15 Ankur Patel

    You can try the following:

    UPDATE TABLE_A 
      SET TABLE_A.COL1 = B.COL_BX
    From 
      TABLE_A A
    INNER JOIN 
      TABLE_B B
      ON B.P1 = A.P1;
    
    UPDATE TABLE_A 
      SET TABLE_A.COL2 = D.COL_DY
    From 
      TABLE_A A
    INNER JOIN 
      TABLE_C C
      ON C.P1 = A.P1
    INNER JOIN 
      TABLE_D D
      ON C.H1 = D.H1;
    
    UPDATE TABLE_A 
      SET TABLE_A.COL3 = G.COL_GZ
    From 
      TABLE_A A
    INNER JOIN 
      TABLE_E E
      ON E.P1 = A.P1
      AND E.P2 = A.P2
    INNER JOIN 
      TABLE_F F
      ON E.N1 = F.N1
    LEFT OUTER JOIN 
      TABLE_G G
      ON G.M1 = F.M1;
    

    I don't know the details about your table structure, but you might be able to combine these 3 updates into one like shown below, but it totally depends on your table structure.

    UPDATE TABLE_A 
      SET 
      TABLE_A.COL1 = B.COL_BX,
      TABLE_A.COL2 = D.COL_DY,
      TABLE_A.COL3 = G.COL_GZ
    From 
      TABLE_A A
    LEFT OUTER JOIN 
      TABLE_B B
      ON B.P1 = A.P1
    LEFT OUTER JOIN 
      TABLE_C C
      ON C.P1 = A.P1
    LEFT OUTER JOIN 
      TABLE_D D
      ON C.H1 = D.H1
    LEFT OUTER JOIN
      TABLE_E E
      ON E.P1 = A.P1
      AND E.P2 = A.P2
    LEFT OUTER JOIN 
      TABLE_F F
      ON E.N1 = F.N1
    LEFT OUTER JOIN 
      TABLE_G G
      ON G.M1 = F.M1;