This discussion is archived
1 2 Previous Next 15 Replies Latest reply: Jan 31, 2013 11:59 AM by Seyed_G RSS

Update fails with ORA-00904 invalid identifier

Seyed_G Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    Solution offered is failing when using the concept to update the real table.
  • 12. Re: Update fails with ORA-00904 invalid identifier
    Etbin Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points