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

oracle, sql

Solution

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.

Problem

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) ```

Original source