SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Update fails with ORA-00904 invalid identifier

Seyed_GJan 28 2013 — edited Jan 31 2013

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

This post has been answered by Frank Kulash on Jan 30 2013

Jump to Answer

Locked Post

New comments cannot be posted to this locked post.

Locked on Feb 28 2013

Added on Jan 28 2013

15 comments

2,532 views