×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Oracle Update with a two table join
2

Oracle Update with a two table join

Oracle Update with a two table join

(OP)
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.



RE: Oracle Update with a two table join

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

CODE

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.

RE: Oracle Update with a two table join

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

CODE

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:

CODE

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:

CODE

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 ) 

CODE

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

RE: Oracle Update with a two table join

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

RE: Oracle Update with a two table join

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

I greatly appreciate it!

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close