title: Update Query - Oracle
body: <p>I am not able to understand the issue with the following SQL query. I am trying to copy column ABC from table TABLE3 to TABLE1 with TABLE2 having the common column between the two.</p>
  
  <pre><code>UPDATE TABLE1 CS  
    SET CS.ABC = TC.ABC  
  WHERE CS.COMMON_COLUMN = (  
      SELECT CGL.COMMON_COLUMN 
      FROM TABLE2 CGL, 
           TABLE3 TC  
      WHERE   CGL.PRD_ID = TC.PRD_ID  
      AND  CGL.PRD_VER = TC.PRD_VER  
      AND  CGL.PY_ID = TC.PY_ID  
      AND  CGL.TPY_ID = TC.TPY_ID  
  )
  </code></pre>
  
  <p>I am running into the error:</p>
  
  <p>SQL Error: ORA-00904: "TC"."ABC": invalid identifier
  00904. 00000 -  "%s: invalid identifier"
  *Cause:<br>
  *Action:</p>
  
  <p><strong>[edit; Please read the explanation below]</strong><br>
  So I have updated the query to make more sense w.r.t my explanation. <code>Table1</code> and <code>Table2</code> are connected by 4 columns <code>PRD_ID, PRD_VER, PY_ID and TPY_ID</code>. This combination finds multiple rows in Table2 since it is not unique/primary key combination. For each row retrieved from Table2, the column <code>common_column</code> is what is needed to update Table3 since <code>common_column</code> only associates with one row.</p>
  
  <p>Example.</p>
  
  <p>Table1</p>
  
  <pre><code> PRD_ID, PRD_VER, PY_ID, TPY_ID, COLUMN_USED_FOR_UPDATE
   ------------------------------------------------------  
        1     , 1      , 1    ,1  ,     VALUE1
        2     , 3      , 4    , 5 ,     VALUE2
  </code></pre>
  
  <p>Table2</p>
  
  <pre><code> PRD_ID,  PRD_VER,  PY_ID,  TPY_ID, COMMON_COLUMN
    ------------------------------------------------
      1  ,     1     , 1,         1,         A
      1  ,     1     , 1,         1,         B
      2,       3     , 4,         5,         C
  </code></pre>
  
  <p>Table 3</p>
  
  <pre><code>        COMMON_COLUMN, .... , COLUMN_TO_UPDATE
  -------------------------------------------------------
             A,         .....  , null
             B,       ....     , null
             C,          .... ,   null
  </code></pre>
  
  <p>So after I execute the query, Table3 should look like this:</p>
  
  <pre><code>        COMMON_COLUMN, .... , COLUMN_TO_UPDATE
  -------------------------------------------------------
             A,         .....  , VALUE1
             B,       ....     , VALUE1
             C,          .... ,   VALUE2
  </code></pre>