1 2 Previous Next 15 Replies Latest reply: Jan 31, 2013 1:59 PM by Seyed_G RSS

    Update fails with ORA-00904 invalid identifier

    Seyed_G
      Hi all,
      I am having problems building an 'update' statement. Running the following select statement,
      SELECT 
       XREF.REGN_ID,
             XREF.COUNTY_NM,
             OWNER.CASE_OWNER_ID,
             OWNER.FORMER_AREA,
             OWNER.AREA,
             OWNER.COUNTY_NUMBER,
             OWNER.OFFICE
        FROM PS2_CASE_OWNER_SEYED OWNER,
             DWFSSD.TAFS_REGN_AREA_XREF_MART XREF
                WHERE    (OWNER.AREA = XREF.AREA_ID(+))
                   AND (OWNER.OFFICE = XREF.OFFICE_CD(+) )
                   AND (OWNER.COUNTY_NUMBER = XREF.COUNTY_NUM(+))
      returns
      REGN_ID   COUNTY_NM                        CASE_OWNER_ID   FORMER_AREA   AREA   COUNTY_NUMBER   OFFICE   
      "1"       "Alfalfa"                        "5008756"       "1"           "1"    "02"            "C"      
      "1"       "Alfalfa"                        "5008954"       "1"           "1"    "02"            "C"      
      "1"       "Beckham"                        "5008803"       "1"           "1"    "05"            "C"      
      "1"       "Beckham"                        "5008222"       "1"           "1"    "05"            "C"      
      "1"       "Beckham"                        "5008223"       "1"           "1"    "05"            "C"      
      "1"       "Beckham"                        "5008424"       "1"           "1"    "05"            "C"      
      "1"       "Beckham"                        "5008442"       "1"           "1"    "05"            "C"      
      "1"       "Beckham"                        "5008780"       "1"           "1"    "05"            "C"      
      "1"       "Beckham"                        "5008787"       "1"           "1"    "05"            "C"      
      "1"       "Beckham"                        "5008788"       "1"           "1"    "05"            "C" 
      I am trying to run the following 'Update' statement to update the PS2_CASE_OWNER_SEYED table,
      UPDATE (
        SELECT OWNER.AREA, XREF.REGN_ID, XREF.COUNTY_NM
        FROM PS2_CASE_OWNER_SEYED OWNER, DWFSSD.TAFS_REGN_AREA_XREF_MART XREF
         WHERE    (OWNER.AREA = XREF.AREA_ID(+))
           AND (OWNER.OFFICE = XREF.OFFICE_CD(+) )
           AND (OWNER.COUNTY_NUMBER = XREF.COUNTY_NUM(+))
      )
      SET OWNER.FORMER_AREA = OWNER.AREA,
          OWNER.AREA = XREF.REGN_ID,
          OWNER.COUNTY_NAME = XREF.COUNTY_NM;
      The 'Update' statement returns the following error:
      ORA-00904: "XREF"."COUNTY_NM": invalid identifier
      Any ideas what is wrong with the update statement?

      Thank you,

      Seyed
        • 1. Re: Update fails with ORA-00904 invalid identifier
          Frank Kulash
          Hi,


          Whenever you have a problem, please post a little sample data (CREATE TABLE and INSERT statements, relevant columns only) from all tables involved.
          Also post the results you want from that data, and an explanation of how you get those results from that data, with specific examples.
          If you're asking about a DML statement, such as UPDATE, the CREATE TABLE and INSERT statements should re-create the tables as they are before the DML, and the results will be the contents of the changed table(s) when everything is finished.
          Always say which version of Oracle you're using (for example, 11.2.0.2.0).
          See the forum FAQ {message:id=9360002}

          You're trying to update an in-line view. The UPDATE statement treats that view as a table that has columns called area, regn_id and county_nm. What tables those values come from, whether they are raw columns or not, and what raw columns they are derived from is all local to the in-line view; the main UPDATE statement doesn't know anything about them; they are out of scope there. So the main UPDATE statement knows about a column called county_nm, but it doesn't know anything about a table called xref.
          • 2. Re: Update fails with ORA-00904 invalid identifier
            Etbin
            Maybe
            UPDATE (SELECT OWNER.FORMER_AREA OFA,OWNER.AREA OA,OWNER.COUNTY_NAME OCN,
                           XREF.REGN_ID XRI,XREF.COUNTY_NM XCN
                      FROM PS2_CASE_OWNER_SEYED OWNER, DWFSSD.TAFS_REGN_AREA_XREF_MART XREF
                     WHERE OWNER.AREA = XREF.AREA_ID(+)
                       AND OWNER.OFFICE = XREF.OFFICE_CD(+)
                       AND OWNER.COUNTY_NUMBER = XREF.COUNTY_NUM(+)
                   )
            SET OFA = OA,
                OA = XRI,
                OCN = XCN
            Regards

            Etbin

            Edited by: Etbin on 28.1.2013 20:53
            Ooops! Should have checked before posting
            • 3. Re: Update fails with ORA-00904 invalid identifier
              Solomon Yakobson
              Both OWNER and XREF are only known inside subquery. You can't reference it outside. You need to alias subquery. And since you are updating OWNER.FORMER_AREA and OWNER.COUNTY_NAME, you must include them into subquery :
              UPDATE (
                SELECT OWNER.AREA, XREF.REGN_ID, XREF.COUNTY_NM XREF_COUNTY_NM,OWNER.FORMER_AREA,OWNER.COUNTY_NAME
                FROM PS2_CASE_OWNER_SEYED OWNER, DWFSSD.TAFS_REGN_AREA_XREF_MART XREF
                 WHERE    (OWNER.AREA = XREF.AREA_ID(+))
                   AND (OWNER.OFFICE = XREF.OFFICE_CD(+) )
                   AND (OWNER.COUNTY_NUMBER = XREF.COUNTY_NUM(+))
              ) T
              SET T.FORMER_AREA = T.AREA,
                  T.AREA = T.REGN_ID,
                  T.COUNTY_NAME = T.XREF_COUNTY_NM;
              /
              But then you'll get more "surprises".

              SY.
              • 4. Re: Update fails with ORA-00904 invalid identifier
                Seyed_G
                Hi Frank,
                Thank you for the information. We are running Oracle 10g. As with regards to sample data and insert statements you suggested, I hesitate to create insert statement from the organization data. I rather use EMP and DEPT tables and duplicate the scenario using them instead.

                Thanks again,


                Seyed
                • 5. Re: Update fails with ORA-00904 invalid identifier
                  Seyed_G
                  Hi Solomon,
                  Thank you for your input, I tried running your suggestion, I got ORA-01779: cannot modify a column which maps to a non key-preserved table.

                  Seyed
                  • 6. Re: Update fails with ORA-00904 invalid identifier
                    Frank Kulash
                    Hi, Seyed,
                    Seyed_G wrote:
                    ... As with regards to sample data and insert statements you suggested, I hesitate to create insert statement from the organization data. I rather use EMP and DEPT tables and duplicate the scenario using them instead.
                    That's fine; in some ways, it's actually better than what I suggested.
                    Instead of CREATE TABLE and INSERT statements for a brand new table, you can post
                    CREATE TABLE  emp
                    AS
                    SELECT  *
                    FROM    scott.emp;
                    and post what you want the changed table(s) to look like after the UPDATE is finished.
                    • 7. Re: Update fails with ORA-00904 invalid identifier
                      Solomon Yakobson
                      Seyed_G wrote:
                      I tried running your suggestion, I got ORA-01779: cannot modify a column which maps to a non key-preserved table.
                      As I said: But then you'll get more "surprises". Read about Key-Preserved Tables.

                      SY.
                      • 8. Re: Update fails with ORA-00904 invalid identifier
                        Seyed_G
                        Hi Frank,
                        Per your suggestion, I am providing the following information to help with asking the question. We are running Oracle 10g. My goal is to build an update statement that accesses both the EMP and DEPT tables and gives everyone working at for example Dallas location a 5% raise and $500 in added commission. I am listing the following scripts to help with creating tables and data needed for answering this question.


                         
                        CREATE TABLE DEPT 
                        ( 
                          DEPTNO  NUMBER(2), 
                          DNAME   VARCHAR2(14 BYTE), 
                          LOC     VARCHAR2(13 BYTE) 
                        ); 
                        
                        
                        CREATE UNIQUE INDEX PK_DEPT ON DEPT 
                        (DEPTNO); 
                        
                        
                        ALTER TABLE DEPT ADD ( 
                          CONSTRAINT PK_DEPT 
                          PRIMARY KEY 
                          (DEPTNO) 
                          USING INDEX PK_DEPT 
                          ENABLE VALIDATE); 
                        
                        
                        Insert into U74981.DEPT 
                           (DEPTNO, DNAME, LOC) 
                        Values 
                           (10, 'ACCOUNTING', 'NEW YORK'); 
                        Insert into U74981.DEPT 
                           (DEPTNO, DNAME, LOC) 
                        Values 
                           (20, 'RESEARCH', 'DALLAS'); 
                        Insert into U74981.DEPT 
                           (DEPTNO, DNAME, LOC) 
                        Values 
                           (30, 'SALES', 'CHICAGO'); 
                        Insert into U74981.DEPT 
                           (DEPTNO, DNAME, LOC) 
                        Values 
                           (40, 'OPERATIONS', 'BOSTON'); 
                        COMMIT; 
                        
                        
                        select * from dept; 
                        
                        
                        DEPTNO,DNAME,LOC 
                        "10","ACCOUNTING","NEW YORK" 
                        "20","RESEARCH","DALLAS" 
                        "30","SALES","CHICAGO" 
                        "40","OPERATIONS","BOSTON" 
                        
                        CREATE TABLE EMP 
                        ( 
                          EMPNO     NUMBER(4), 
                          ENAME     VARCHAR2(10 BYTE), 
                          JOB       VARCHAR2(9 BYTE), 
                          MGR       NUMBER(4), 
                          HIREDATE  DATE, 
                          SAL       NUMBER(7,2), 
                          COMM      NUMBER(7,2), 
                          DEPTNO    NUMBER(2) 
                        ); 
                        
                        
                        CREATE UNIQUE INDEX PK_EMP ON EMP 
                        (EMPNO); 
                        
                        
                        ALTER TABLE EMP ADD ( 
                          CONSTRAINT PK_EMP 
                          PRIMARY KEY 
                          (EMPNO) 
                          USING INDEX PK_EMP 
                          ENABLE VALIDATE); 
                        
                        ALTER TABLE EMP ADD ( 
                          CONSTRAINT FK_DEPTNO 
                          FOREIGN KEY (DEPTNO) 
                          REFERENCES DEPT (DEPTNO) 
                          ENABLE VALIDATE); 
                        
                        
                        
                        SET DEFINE OFF; 
                        Insert into U74981.EMP 
                           (EMPNO, ENAME, JOB, MGR, HIREDATE, 
                            SAL, DEPTNO) 
                        Values 
                           (7369, 'SMITH', 'CLERK', 7902, TO_DATE('12/17/1980 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 
                            800, 20); 
                        Insert into U74981.EMP 
                           (EMPNO, ENAME, JOB, MGR, HIREDATE, 
                            SAL, COMM, DEPTNO) 
                        Values 
                           (7499, 'ALLEN', 'SALESMAN', 7698, TO_DATE('02/20/1981 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 
                            1600, 300, 30); 
                        Insert into U74981.EMP 
                           (EMPNO, ENAME, JOB, MGR, HIREDATE, 
                            SAL, COMM, DEPTNO) 
                        Values 
                           (7521, 'WARD', 'SALESMAN', 7698, TO_DATE('02/22/1981 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 
                            1250, 500, 30); 
                        Insert into U74981.EMP 
                           (EMPNO, ENAME, JOB, MGR, HIREDATE, 
                            SAL, DEPTNO) 
                        Values 
                           (7566, 'JONES', 'MANAGER', 7839, TO_DATE('04/02/1981 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 
                            2975, 20); 
                        Insert into U74981.EMP 
                           (EMPNO, ENAME, JOB, MGR, HIREDATE, 
                            SAL, COMM, DEPTNO) 
                        Values 
                           (7654, 'MARTIN', 'SALESMAN', 7698, TO_DATE('09/28/1981 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 
                            1250, 1400, 30); 
                        Insert into U74981.EMP 
                           (EMPNO, ENAME, JOB, MGR, HIREDATE, 
                            SAL, DEPTNO) 
                        Values 
                           (7698, 'BLAKE', 'MANAGER', 7839, TO_DATE('05/01/1981 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 
                            2850, 30); 
                        Insert into U74981.EMP 
                           (EMPNO, ENAME, JOB, MGR, HIREDATE, 
                            SAL, DEPTNO) 
                        Values 
                           (7782, 'CLARK', 'MANAGER', 7839, TO_DATE('06/09/1981 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 
                            2450, 10); 
                        Insert into U74981.EMP 
                           (EMPNO, ENAME, JOB, MGR, HIREDATE, 
                            SAL, DEPTNO) 
                        Values 
                           (7788, 'SCOTT', 'ANALYST', 7566, TO_DATE('04/19/1987 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 
                            3000, 20); 
                        Insert into U74981.EMP 
                           (EMPNO, ENAME, JOB, HIREDATE, SAL, 
                            DEPTNO) 
                        Values 
                           (7839, 'KING', 'PRESIDENT', TO_DATE('11/17/1981 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 5000, 
                            10); 
                        Insert into U74981.EMP 
                           (EMPNO, ENAME, JOB, MGR, HIREDATE, 
                            SAL, COMM, DEPTNO) 
                        Values 
                           (7844, 'TURNER', 'SALESMAN', 7698, TO_DATE('09/08/1981 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 
                            1500, 0, 30); 
                        Insert into U74981.EMP 
                           (EMPNO, ENAME, JOB, MGR, HIREDATE, 
                            SAL, DEPTNO) 
                        Values 
                           (7876, 'ADAMS', 'CLERK', 7788, TO_DATE('05/23/1987 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 
                            1100, 20); 
                        Insert into U74981.EMP 
                           (EMPNO, ENAME, JOB, MGR, HIREDATE, 
                            SAL, DEPTNO) 
                        Values 
                           (7900, 'JAMES', 'CLERK', 7698, TO_DATE('12/03/1981 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 
                            950, 30); 
                        Insert into U74981.EMP 
                           (EMPNO, ENAME, JOB, MGR, HIREDATE, 
                            SAL, DEPTNO) 
                        Values 
                           (7902, 'FORD', 'ANALYST', 7566, TO_DATE('12/03/1981 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 
                            3000, 20); 
                        Insert into U74981.EMP 
                           (EMPNO, ENAME, JOB, MGR, HIREDATE, 
                            SAL, DEPTNO) 
                        Values 
                           (7934, 'MILLER', 'CLERK', 7782, TO_DATE('01/23/1982 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 
                            1300, 10); 
                        COMMIT; 
                        
                        SELECT * FROM EMP; 
                        
                        EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO 
                        "7369","SMITH","CLERK","7902",12/17/1980,"800",,"20" 
                        "7499","ALLEN","SALESMAN","7698",2/20/1981,"1600","300","30" 
                        "7521","WARD","SALESMAN","7698",2/22/1981,"1250","500","30" 
                        "7566","JONES","MANAGER","7839",4/2/1981,"2975",,"20" 
                        "7654","MARTIN","SALESMAN","7698",9/28/1981,"1250","1400","30" 
                        "7698","BLAKE","MANAGER","7839",5/1/1981,"2850",,"30" 
                        "7782","CLARK","MANAGER","7839",6/9/1981,"2450",,"10" 
                        "7788","SCOTT","ANALYST","7566",4/19/1987,"3000",,"20" 
                        "7839","KING","PRESIDENT",,11/17/1981,"5000",,"10" 
                        "7844","TURNER","SALESMAN","7698",9/8/1981,"1500","0","30" 
                        "7876","ADAMS","CLERK","7788",5/23/1987,"1100",,"20" 
                        "7900","JAMES","CLERK","7698",12/3/1981,"950",,"30" 
                        "7902","FORD","ANALYST","7566",12/3/1981,"3000",,"20" 
                        "7934","MILLER","CLERK","7782",1/23/1982,"1300",,"10" 
                        I want to build an update statement to for example give everyone at the Dallas location a 5% raise and add $500 in commission. When the update is successfull the result should look like the following:
                         
                        
                        SELECT EMPNO,
                               ENAME,
                               JOB,
                               MGR,
                               HIREDATE,
                               DECODE(LOC,'DALLAS',DECODE(SAL, NULL, 0, SAL*1.05),SAL) "SAL",
                               DECODE(LOC,'DALLAS',DECODE(COMM,NULL, 500,COMM+500),COMM) "COMM",
                               EMP.DEPTNO
                        
                        
                          FROM DEPT INNER JOIN EMP ON (DEPT.DEPTNO = EMP.DEPTNO);
                        
                        
                        EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO 
                        "7369","SMITH","CLERK","7902",12/17/1980,"840","500","20" 
                        "7499","ALLEN","SALESMAN","7698",2/20/1981,"1600","300","30" 
                        "7521","WARD","SALESMAN","7698",2/22/1981,"1250","500","30" 
                        "7566","JONES","MANAGER","7839",4/2/1981,"3123.75";,"500","20" 
                        "7654","MARTIN","SALESMAN","7698",9/28/1981,"1250","1400","30" 
                        "7698","BLAKE","MANAGER","7839",5/1/1981,"2850",,"30" 
                        "7782","CLARK","MANAGER","7839",6/9/1981,"2450",,"10" 
                        "7788","SCOTT","ANALYST","7566",4/19/1987,"3150","500","20" 
                        "7839","KING","PRESIDENT",,11/17/1981,"5000",,"10" 
                        "7844","TURNER","SALESMAN","7698",9/8/1981,"1500","0","30" 
                        "7876","ADAMS","CLERK","7788",5/23/1987,"1155","500","20" 
                        "7900","JAMES","CLERK","7698",12/3/1981,"950",,"30" 
                        "7902","FORD","ANALYST","7566",12/3/1981,"3150","500","20" 
                        "7934","MILLER","CLERK","7782",1/23/1982,"1300",,"10" 
                        Thank you for your help,


                        Seyed
                        • 9. Re: Update fails with ORA-00904 invalid identifier
                          Frank Kulash
                          Hi, Seyed,

                          Here's one way to do that UPDATE:
                          UPDATE     emp
                          SET     sal     = sal * 1.05
                          ,     comm     = NVL (comm, 0) + 500
                          WHERE     deptno     IN (
                                         SELECT     deptno
                                         FROM     dept
                                         WHERE     loc     = 'DALLAS'
                                       )
                          ;
                          If you want to use an in-line view, you can do this:
                          UPDATE     (
                                    SELECT     e.sal
                                    ,     e.comm
                                    FROM     emp     e
                                    JOIN     dept     d  ON     d.deptno  = e.deptno
                                    WHERE     d.loc     = 'DALLAS'
                               )
                          SET     sal     = sal * 1.05
                          ,     comm     = NVL (comm, 0) + 500
                          ;
                          Either way, the emp table is left the way you wanted it:
                          EMPNO ENAME  JOB         MGR HIREDATE          SAL  COMM DEPTNO
                          ----- ------ --------- ----- ---------- ---------- ----- ------
                           7782 CLARK  MANAGER    7839 06/09/1981       2450           10
                           7839 KING   PRESIDENT       11/17/1981       5000           10
                           7934 MILLER CLERK      7782 01/23/1982       1300           10
                           7876 ADAMS  CLERK      7788 05/23/1987       1155   500     20
                           7902 FORD   ANALYST    7566 12/03/1981       3150   500     20
                           7566 JONES  MANAGER    7839 04/02/1981    3123.75   500     20
                           7788 SCOTT  ANALYST    7566 04/19/1987       3150   500     20
                           7369 SMITH  CLERK      7902 12/17/1980        840   500     20
                           7499 ALLEN  SALESMAN   7698 02/20/1981       1600   300     30
                           7698 BLAKE  MANAGER    7839 05/01/1981       2850           30
                           7900 JAMES  CLERK      7698 12/03/1981        950           30
                           7654 MARTIN SALESMAN   7698 09/28/1981       1250  1400     30
                           7844 TURNER SALESMAN   7698 09/08/1981       1500     0     30
                           7521 WARD   SALESMAN   7698 02/22/1981       1250   500     30
                          • 10. Re: Update fails with ORA-00904 invalid identifier
                            Seyed_G
                            Hi Frank,
                            I really appreciate your help. This is exactly what I wanted to accomplish. I will now use one of your suggested techniques to update our organization's real table.

                            Health and Happiness,

                            Seyed
                            • 11. Re: Update fails with ORA-00904 invalid identifier
                              Seyed_G
                              Solution offered is failing when using the concept to update the real table.
                              • 12. Re: Update fails with ORA-00904 invalid identifier
                                Etbin
                                you can check whether or not your columns are updatable by looking into http://docs.oracle.com/cd/E11882_01/server.112/e25513/statviews_2128.htm#i1593275

                                Regards

                                Etbin
                                • 13. Re: Update fails with ORA-00904 invalid identifier
                                  Seyed_G
                                  Hi Etbin,
                                  Thank you for your assistance with this problem. I tried applying Frank's solution to a different table, but getting ORA-01779: cannot modify a column which maps to a non key-preserved table. Below, I am listing the update statement that is failing with the error and description of the two tables that I was working on.
                                  UPDATE    (
                                  
                                              SELECT 
                                               XREF.REGN_ID,
                                                     XREF.COUNTY_NM,
                                                     OWNER.CASE_OWNER_ID,
                                                     OWNER.FORMER_AREA,
                                                     OWNER.AREA,
                                                     OWNER.COUNTY_NUMBER,
                                                     OWNER.OFFICE,
                                                     OWNER.COUNTY_NAME
                                                FROM PS2_CASE_OWNER_SEYED3 OWNER,
                                                     TAFS_REGN_AREA_XREF_MART3 XREF
                                                        WHERE    (OWNER.AREA = XREF.AREA_ID)
                                                           AND (OWNER.OFFICE = XREF.OFFICE_CD )
                                                           AND (OWNER.COUNTY_NUMBER = XREF.COUNTY_NUM)
                                  
                                      )
                                  SET  FORMER_AREA = AREA
                                  ,    AREA = REGN_ID
                                  ,    COUNTY_NAME = COUNTY_NM
                                  
                                  ;
                                  results in
                                  ORA-01779: cannot modify a column which maps to a non key-preserved table
                                  Table to be updated:
                                  CREATE TABLE PS2_CASE_OWNER_SEYED3
                                  (
                                    CASE_OWNER_ID  NUMBER(10)                     NOT NULL,
                                    FORMER_AREA    CHAR(1 BYTE),
                                    AREA           CHAR(1 BYTE),
                                    COUNTY_NUMBER  CHAR(2 BYTE),
                                    OFFICE         CHAR(1 BYTE),
                                    SUPVR_NUMBER   CHAR(2 BYTE),
                                    WKR_NUMBER     CHAR(2 BYTE),
                                    COUNTY_NAME    VARCHAR2(30 BYTE),
                                    JOB_NUMBER     NUMBER(10)
                                  )
                                  PCTUSED    40
                                  PCTFREE    10
                                  INITRANS   1
                                  MAXTRANS   255
                                  STORAGE    (
                                              INITIAL          64K
                                              MINEXTENTS       1
                                              MAXEXTENTS       UNLIMITED
                                              PCTINCREASE      0
                                              FREELISTS        1
                                              FREELIST GROUPS  1
                                              BUFFER_POOL      DEFAULT
                                             )
                                  LOGGING 
                                  NOCOMPRESS 
                                  NOCACHE
                                  NOPARALLEL
                                  MONITORING;
                                  
                                  
                                  CREATE UNIQUE INDEX IDXPK_PS2_CASE_OWNER_SEYED3 ON PS2_CASE_OWNER_SEYED3
                                  (CASE_OWNER_ID)
                                  LOGGING
                                  PCTFREE    10
                                  INITRANS   2
                                  MAXTRANS   255
                                  STORAGE    (
                                              INITIAL          10000K
                                              MINEXTENTS       1
                                              MAXEXTENTS       UNLIMITED
                                              PCTINCREASE      0
                                              FREELISTS        1
                                              FREELIST GROUPS  1
                                              BUFFER_POOL      DEFAULT
                                             )
                                  NOPARALLEL;
                                  
                                  
                                  ALTER TABLE PS2_CASE_OWNER_SEYED3 ADD (
                                    CONSTRAINT IDXPK_PS2_CASE_OWNER_SEYED3
                                    PRIMARY KEY
                                    (CASE_OWNER_ID)
                                    USING INDEX IDXPK_PS2_CASE_OWNER_SEYED3
                                    ENABLE VALIDATE);
                                  Table used for lookup:
                                  CREATE TABLE TAFS_REGN_AREA_XREF_MART3
                                  (
                                    REGN_AREA_XREF_SEQ_ID  INTEGER,
                                    COUNTY_NUM             CHAR(2 BYTE)           NOT NULL,
                                    OFFICE_CD              CHAR(1 BYTE)           NOT NULL,
                                    COUNTY_NM              VARCHAR2(30 BYTE),
                                    REGN_ID                CHAR(1 BYTE)           NOT NULL,
                                    AREA_ID                CHAR(1 BYTE)           NOT NULL,
                                    CREATE_DT              TIMESTAMP(0)           NOT NULL,
                                    CREATE_USER            CHAR(8 BYTE)           NOT NULL,
                                    CREATE_TYP_CD          CHAR(2 BYTE)           NOT NULL,
                                    CREATE_NM              VARCHAR2(30 BYTE)      NOT NULL,
                                    UPDATE_DT              TIMESTAMP(0)           NOT NULL,
                                    UPDATE_USER            CHAR(8 BYTE)           NOT NULL,
                                    UPDATE_TYP_CD          CHAR(2 BYTE)           NOT NULL,
                                    UPDATE_NM              VARCHAR2(30 BYTE)      NOT NULL
                                  )
                                  
                                  PCTUSED    40
                                  PCTFREE    10
                                  INITRANS   1
                                  MAXTRANS   255
                                  STORAGE    (
                                              INITIAL          64K
                                              MINEXTENTS       1
                                              MAXEXTENTS       UNLIMITED
                                              PCTINCREASE      0
                                              FREELISTS        1
                                              FREELIST GROUPS  1
                                              BUFFER_POOL      DEFAULT
                                             )
                                  LOGGING 
                                  NOCOMPRESS 
                                  NOCACHE
                                  NOPARALLEL
                                  MONITORING;
                                  
                                  
                                  CREATE UNIQUE INDEX TAFS_REGN_AREA_XREF_MART3_PK ON TAFS_REGN_AREA_XREF_MART3
                                  (REGN_AREA_XREF_SEQ_ID)
                                  LOGGINGR
                                  PCTFREE    10
                                  INITRANS   2
                                  MAXTRANS   255
                                  STORAGE    (
                                              INITIAL          64K
                                              MINEXTENTS       1
                                              MAXEXTENTS       UNLIMITED
                                              PCTINCREASE      0
                                              FREELISTS        1
                                              FREELIST GROUPS  1
                                              BUFFER_POOL      DEFAULT
                                             )
                                  NOPARALLEL;
                                  
                                  
                                  ALTER TABLE TAFS_REGN_AREA_XREF_MART3 ADD (
                                    CONSTRAINT TAFS_REGN_AREA_XREF_MART3_PK
                                    PRIMARY KEY
                                    (REGN_AREA_XREF_SEQ_ID)
                                    USING INDEX TAFS_REGN_AREA_XREF_MART3_PK
                                    ENABLE VALIDATE);
                                  Per your advice, I checked to see if these columns were updatable, and they were. Please see below:
                                  select table_name, column_name, updatable, insertable, deletable from ALL_UPDATABLE_COLUMNS where table_name = 'PS2_CASE_OWNER_SEYED3'
                                  and here is the result:
                                  TABLE_NAME,COLUMN_NAME,UPDATABLE,INSERTABLE,DELETABLE
                                  "PS2_CASE_OWNER_SEYED3","JOB_NUMBER","YES","YES","YES"
                                  "PS2_CASE_OWNER_SEYED3","COUNTY_NAME","YES","YES","YES"
                                  "PS2_CASE_OWNER_SEYED3","WKR_NUMBER","YES","YES","YES"
                                  "PS2_CASE_OWNER_SEYED3","SUPVR_NUMBER","YES","YES","YES"
                                  "PS2_CASE_OWNER_SEYED3","OFFICE","YES","YES","YES"
                                  "PS2_CASE_OWNER_SEYED3","COUNTY_NUMBER","YES","YES","YES"
                                  "PS2_CASE_OWNER_SEYED3","AREA","YES","YES","YES"
                                  "PS2_CASE_OWNER_SEYED3","FORMER_AREA","YES","YES","YES"
                                  "PS2_CASE_OWNER_SEYED3","CASE_OWNER_ID","YES","YES","YES"
                                  Thank you again,

                                  Seyed
                                  • 14. Re: Update fails with ORA-00904 invalid identifier
                                    Frank Kulash
                                    Hi, Seyed,

                                    You can only update an in-line view under particular circumstances.
                                    In general, UPDATE the table directly, using a sub-query, as in my other example.
                                    Often, a MERGE statement is more convenient than an UPDATE, though either could be used. In the example you gave before, an UPDATE was definitely simpler. In this example, it looks like MERGE would be better:
                                    MERGE INTO  ps2_case_owner_seyed3     dst
                                    USING  (
                                                SELECT 
                                                       XREF.REGN_ID,
                                                       XREF.COUNTY_NM,
                                                       OWNER.CASE_OWNER_ID,
                                    --                 OWNER.FORMER_AREA,          -- Not needed
                                                       OWNER.AREA
                                    --              ,  OWNER.COUNTY_NUMBER,          -- Not needed
                                    --                 OWNER.OFFICE,          -- Not needed
                                    --                 OWNER.COUNTY_NAME          -- Not needed
                                                  FROM PS2_CASE_OWNER_SEYED3 OWNER,
                                                       TAFS_REGN_AREA_XREF_MART3 XREF
                                                          WHERE    (OWNER.AREA = XREF.AREA_ID)
                                                             AND (OWNER.OFFICE = XREF.OFFICE_CD )
                                                             AND (OWNER.COUNTY_NUMBER = XREF.COUNTY_NUM)
                                           )                         src
                                    ON     (dst.case_owner_id     = src.case_owner_id)
                                    WHEN MATCHED THEN UPDATE
                                    SET    dst.former_area     = src.area
                                    ,      dst.area          = src.regn_id
                                    ,      dst.county_name     = src.county_nm
                                    ;
                                    You didn't post any sample data, or the results you want from that data, so I can't tell if this is exactly what you want or not.

                                    Notice that the USING clause is essentially what you were using as the in-line view, except that I removed 4 columns from the SELECT clause. If columns (such as OFFICE in this example) aren't used outside the USING clause, then you're only wasting resources by having the USING clause find and return those columns. Including them won't make the MERGE statement produce the wrong results, it will just make the statement run slower.
                                    1 2 Previous Next