CODEWITHSUNDEEP
sqloracle-database
Charles
Charles

Reputation: 47

Oracle - How to use merge to update a column based on the values from other table and columns

I would like to update the values in closed_date column based on the values comparison from other columns and other table. I used Oracle merge into statement. But it gave me an error:

Error: ORA-00969: missing ON keyword

I am not sure what goes wrong. Do I miss anything? Below is my script:

MERGE INTO PR_DMN dmn
USING (select alg.PR_DMN_ID, alg.PR_ACTIVITY_ID, alg.ACTIVITY_TS from PR_ACTIVITY_LOG) alg
ON dmn.PR_DMN_ID = alg.PR_DMN_ID
-- update
WHEN MATCHED THEN
UPDATE SET dmn.CLOSED_DATE =
    (CASE 
        WHEN alg.PR_ACTIVITY_ID IN ('10009', '10010', '10011', '10013') THEN alg.ACTIVITY_TS
        WHEN alg.PR_ACTIVITY_ID = '10005' AND dmn.CONT_RESP_TS <= dmn.CONT_RESP_DUE_TS THEN dmn.CONT_RESP_TS 
        WHEN alg.PR_ACTIVITY_ID = '10008' AND dmn.CORR_RESP_TS <= dmn.CORR_RESP_DUE_TS THEN dmn.CORR_RESP_TS
        ELSE dmn.CLOSED_DATE 
    END)

Upvotes: 4

Views: 38890

Answers (1)

Ben
Ben

Reputation: 52863

You have two errors, as you can see with a simple example. Firstly the on clause needs to be wrapped in parenthesis. Secondly, you can't reference the alias of the sub-select in the using clause within that sub-query.

If I set up a simple example using your table names as follows:

create table pr_dmn as
 select level as a, sysdate as b
   from dual
connect by level <= 10;

Table created.


create table PR_ACTIVITY_LOG as
 select level as a, sysdate as b
   from dual
connect by level <= 20;

Table created.

Then execute the correct query it should work:

merge into pr_dmn dmn
 using (select a, b from pr_activity_log) alg -- no alg. inside the sub-query
    on (dmn.a = alg.a) -- wrapped in parenthesis
  when matched then
update set dmn.b = alg.b
       ;

10 rows merged.

I always find PSOUG a good reference for things like this, though the documentation has some good examples as well.

Upvotes: 6

Related Questions