Oracle Update with a two table join 2

[sa0309]

I'm trying to update table CUST_CLN_A1166_CCLF8_ALIGN with the update statement below:

UPDATE
(
SELECT ALGN.PERSON_ID
, ALGN.BENE_HIC_NUM
, TMP.PERSON_ID
, TMP.NEW_BENE_HIC_NUM
FROM CUST_CLN_A1166_CCLF8_ALIGN ALGN
, EDW_TMP_CCLF8_ALIGN TMP
WHERE ALGN.BENE_KEY = TMP.TMP_BENE_KEY
)
SET ALGN.PERSON_ID = TMP.PERSON_ID
, ALGN.BENE_HIC_NUM = TMP.NEW_BENE_HIC_NUM
;

However; I get the following error:

Error starting at line 1 in command:
UPDATE
(
SELECT ALGN.PERSON_ID
, ALGN.BENE_HIC_NUM
, TMP.PERSON_ID
, TMP.NEW_BENE_HIC_NUM
FROM CUST_CLN_A1166_CCLF8_ALIGN ALGN
, EDW_TMP_CCLF8_ALIGN TMP
WHERE ALGN.BENE_KEY = TMP.TMP_BENE_KEY
)
SET ALGN.PERSON_ID = TMP.PERSON_ID
, ALGN.BENE_HIC_NUM = TMP.NEW_BENE_HIC_NUM
Error at Command Line:12 Column:30
Error report:
SQL Error: ORA-00904: "TMP"."NEW_BENE_HIC_NUM": invalid identifier
00904. 00000 - "%s: invalid identifier"
*Cause:
*Action:
committed.


I've verified all column names, etc. What am I missing?

Thanks in advance.

 

[carp]

sa-
You are trying to update a SELECT statement; the command needs to update either a table or (if possible) a view. So since you want to update CUST_CLN_A1166_CCLF8_ALIGN, that is what your command should say.
Try something like

UPDATE cust_cln_a1166_cclf8_align algn
   SET person_id = (SELECT person_id 
                      FROM edw_tmp_cclf8_align_tmp
                     WHERE algn.bene_key = tmp.tmp_bene_key);

Note that you are now telling Oracle to update a specific table. For each record that has a matching bene_key in your tmp table, a lookup will be performed to find the corresponding person_id and this change will be applied. This is called a correlated subquery.

Please let us know if this solves your issue.

 

[ChrisHunt]

This should work, provided there's a primary key constraint on CUST_CLN_A1166_CCLF8_ALIGN:

UPDATE 
( 
SELECT ALGN.PERSON_ID
, ALGN.BENE_HIC_NUM
, TMP.PERSON_ID NEW_PERSON_ID
, TMP.NEW_BENE_HIC_NUM
FROM CUST_CLN_A1166_CCLF8_ALIGN ALGN
, EDW_TMP_CCLF8_ALIGN TMP
WHERE ALGN.BENE_KEY = TMP.TMP_BENE_KEY 
) x
SET x.PERSON_ID = x.NEW_PERSON_ID
, x.BENE_HIC_NUM = x.NEW_BENE_HIC_NUM

The important thing to realise is that the bracketed SELECT statement is an inline view upon which the INSERT statement operates. It's equivalent to doing these three statements:

CREATE OR REPLACE VIEW x AS 
SELECT ALGN.PERSON_ID
, ALGN.BENE_HIC_NUM
, TMP.PERSON_ID NEW_PERSON_ID
, TMP.NEW_BENE_HIC_NUM
FROM CUST_CLN_A1166_CCLF8_ALIGN ALGN
, EDW_TMP_CCLF8_ALIGN TMP
WHERE ALGN.BENE_KEY = TMP.TMP_BENE_KEY 
/
UPDATE x
SET x.PERSON_ID = x.NEW_PERSON_ID
, x.BENE_HIC_NUM = x.NEW_BENE_HIC_NUM
/
DROP VIEW x

If you can't get the "update a view" approach to work, here's a couple of other ways to do it:

UPDATE CUST_CLN_A1166_CCLF8_ALIGN ALGN
SET (ALGN.PERSON_ID,ALGN.BENE_HIC_NUM) = 
   (SELECT TMP.PERSON_ID 
         , TMP.NEW_BENE_HIC_NUM
    FROM  EDW_TMP_CCLF8_ALIGN TMP
    WHERE ALGN.BENE_KEY = TMP.TMP_BENE_KEY)
WHERE ALGN.BENE_KEY IN (SELECT TMP_BENE_KEY FROM EDW_TMP_CCLF8_ALIGN )

MERGE INTO  CUST_CLN_A1166_CCLF8_ALIGN ALGN USING
  (SELECT TMP.TMP_BENE_KEY
        , TMP.PERSON_ID 
        , TMP.NEW_BENE_HIC_NUM
   FROM CUST_CLN_A1166_CCLF8_ALIGN ALGN2
   WHERE ALGN2.BENE_KEY = TMP.TMP_BENE_KEY) x
ON (x.TMP_BENE_KEY = ALGN.BENE_KEY)
WHEN MATCHED THEN UPDATE
   SET ALGN.PERSON_ID = x.PERSON_ID,
       ALGN.BENE_HIC_NUM = x.NEW_BENE_HIC_NUM

-- Chris Hunt
Webmaster & Tragedian
Extra Connections Ltd

 

[carp]

Chris -
Have a star - and thanks for showing an old dog a new trick!

 

[sa0309]

Chris - thank you for the code samples. The 3rd one worked perfectly for me.

I greatly appreciate it!