Update Query - Oracle

Question

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.

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  
)

I am running into the error:

SQL Error: ORA-00904: "TC"."ABC": invalid identifier 00904. 00000 - "%s: invalid identifier" *Cause:
*Action:

[edit; Please read the explanation below]
So I have updated the query to make more sense w.r.t my explanation. Table1 and Table2 are connected by 4 columns PRD_ID, PRD_VER, PY_ID and TPY_ID. This combination finds multiple rows in Table2 since it is not unique/primary key combination. For each row retrieved from Table2, the column common_column is what is needed to update Table3 since common_column only associates with one row.

Example.

Table1

 PRD_ID, PRD_VER, PY_ID, TPY_ID, COLUMN_USED_FOR_UPDATE
 ------------------------------------------------------  
      1     , 1      , 1    ,1  ,     VALUE1
      2     , 3      , 4    , 5 ,     VALUE2

Table2

 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

Table 3

        COMMON_COLUMN, .... , COLUMN_TO_UPDATE
-------------------------------------------------------
           A,         .....  , null
           B,       ....     , null
           C,          .... ,   null

So after I execute the query, Table3 should look like this:

        COMMON_COLUMN, .... , COLUMN_TO_UPDATE
-------------------------------------------------------
           A,         .....  , VALUE1
           B,       ....     , VALUE1
           C,          .... ,   VALUE2

Justin Cave · Accepted Answer · 2011-11-05 20:17:05Z

6

I'm guessing that you want

UPDATE table1 cs
   SET cs.abc = (SELECT tc.abc
                   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.typ_id       = tc.tpy_id
                    AND cd.common_column = cgl.common_column)
 WHERE EXISTS (SELECT 1
                 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.typ_id       = tc.tpy_id
                  AND cd.common_column = cgl.common_column)

Update: Other than the changes to the column and table names, my initial answer appears to work with the sample data you posted. Note that it's always easier to post DDL and DML so that we can reproduce your tables and data rather than having us convert your data to DDL and DML.

If I create your tables and data

SQL> create table table1 (
  2    prd_id number,
  3    prd_ver number,
  4    py_id number,
  5    typ_id number,
  6    column_used_for_update varchar2(10)
  7  );

Table created.

SQL> begin
  2    insert into table1 values( 1, 1, 1, 1, 'VALUE1' );
  3    insert into table1 values( 2, 3, 4, 5, 'VALUE2' );
  4  end;
  5  /

PL/SQL procedure successfully completed.

SQL> create table table2 (
  2    prd_id number,
  3    prd_ver number,
  4    py_id number,
  5    typ_id number,
  6    common_column varchar2(10)
  7  );

Table created.

SQL> begin
  2    insert into table2 values( 1, 1, 1, 1, 'A' );
  3    insert into table2 values( 1, 1, 1, 1, 'B' );
  4    insert into table2 values( 2, 3, 4, 5, 'C' );
  5  end;
  6  /

PL/SQL procedure successfully completed.

SQL> create table table3 (
  2    common_column varchar2(10),
  3    column_to_update varchar2(10)
  4  );

Table created.

SQL> begin
  2    insert into table3 values( 'A', null );
  3    insert into table3 values( 'B', null );
  4    insert into table3 values( 'C', null );
  5  end;
  6  /

PL/SQL procedure successfully completed.

SQL> commit;

Commit complete.

Then adjust the table and column names from my initial answer, it appears that the update works correctly

SQL> ed
Wrote file afiedt.buf

  1  UPDATE table3 t3
  2     SET t3.column_to_update = (
  3                   SELECT t1.column_used_for_update
  4                     FROM table2 t2,
  5                          table1 t1
  6                    WHERE t1.prd_id        = t2.prd_id
  7                      AND t1.prd_ver       = t2.prd_ver
  8                      AND t1.py_id         = t2.py_id
  9                      AND t1.typ_id        = t2.typ_id
 10                      AND t3.common_column = t2.common_column)
 11   WHERE EXISTS (  SELECT 1
 12                     FROM table2 t2,
 13                          table1 t1
 14                    WHERE t1.prd_id        = t2.prd_id
 15                      AND t1.prd_ver       = t2.prd_ver
 16                      AND t1.py_id         = t2.py_id
 17                      AND t1.typ_id        = t2.typ_id
 18*                     AND t3.common_column = t2.common_column)
SQL> /

3 rows updated.

SQL> select * from table3;

COMMON_COL COLUMN_TO_
---------- ----------
A          VALUE1
B          VALUE1
C          VALUE2

edited Nov 5, 2011 at 20:17

answered Nov 4, 2011 at 20:16

Justin Cave

233k25 gold badges378 silver badges395 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

name_masked Over a year ago

Thank you Justin. I am running into the issue since the inner query returns more then one row. How can I accommodate that to update individual rows?

Justin Cave Over a year ago

@darkie15 - If the inner query returns multiple values, what value do you want to use to update the row? You'll want to encode that logic in the inner query so that it returns just a single value for any row of table1

name_masked Over a year ago

Justin, I have provided more detailed information to explain my requirements.

Justin Cave Over a year ago

@darkie15 - Updated my answer with the changes to the table and column names changes from your update. Are you sure the problems you're having are produced with the sample data you posted?

Collectives™ on Stack Overflow

Update Query - Oracle

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related