Convert MERGE to UPDATE-CodePudding

I have the following MERGE statement.Is there a way to convert this into an update statement without using MERGE?

MERGE INTO tab1
USING (SELECT tab1.col1, tab2.col2
         FROM tab1, tab2
        WHERE tab1.col1 = tab2.col1) tab3
   ON (tab1.col1 = tab3.col1)
 WHEN MATCHED THEN UPDATE SET col2 = tab3.col2

CodePudding user response：

What you are asking about is called "update through join", and contrary to a widely held belief, it is possible in Oracle. But there is a catch.

Obviously, the update - no matter how you attempt to perform it - is not well defined unless column col1 is unique in table tab2. That column is used for lookup in the update process; if its values are not unique, the update will be ambiguous. I ignore here idiotic retorts such as "uniqueness is needed only for those values also found in tab1.col1", or "there is no ambiguity as long as all values in tab2.col2 are equal when the corresponding values in tab2.col1 are equal".

The "catch" is this. The uniqueness of tab2.col1 may be a matter of data (you know it when you inspect the data), or a matter of metadata (there is a unique constraint, or a unique index, or a PK constraint, etc., on tab2.col1, which the parser can inspect without ever looking at the actual data).

merge will work even when uniqueness is only known by inspecting the data. It will still throw an error if uniqueness is violated - but that will be a runtime error (only after the data in tab2 is accessed from disk). By contrast, updating through a join requires the same uniqueness to be known ahead of time, through the metadata (or in other ways: for example if the second rowset - not a table but the table-like result of a query - is the result of an aggregation grouping on the join column; then the uniqueness is guaranteed by the definition of "aggregation").

Here is a brief example to show the difference.

Test data:

create table tab1 (col1 number, col2 number);
insert into tab1 (col1, col2) values (1, 3);

create table tab2 (col1 number, col2 number);
insert into tab2 (col1, col2) values (1, 6);

commit;

merge statement (with check at the end):

merge into tab1
using(
select tab1.col1,
       tab2.col2
from tab1,tab2 
where tab1.col1 = tab2.col1) tab3
on(tab1.col1 = tab3.col1)
when matched then
update 
set col2 = tab3.col2;

1 row merged.

select * from tab1;

      COL1       COL2
---------- ----------
         1          6

Now let's restore table tab1 to its original data for the next test(s):

rollback;

select * from tab1;

      COL1       COL2
---------- ----------
         1          3

Update through join - with no uniqueness guaranteed in the metadata (will result in error):

update
  ( select t1.col2 as t1_c2, t2.col2 as t2_c2
    from   tab1 t1 join tab2 t2 on t1.col1 = t2.col1
  )
set t1_c2 = t2_c2; 

Error report -
SQL Error: ORA-01779: cannot modify a column which maps to a non key-preserved table
01779. 00000 -  "cannot modify a column which maps to a non key-preserved table"
*Cause:    An attempt was made to insert or update columns of a join view which
           map to a non-key-preserved table.
*Action:   Modify the underlying base tables directly.

Now let's add a unique constraint on the lookup column:

alter table tab2 modify (col1 unique);

Table TAB2 altered.

and try the update again (with the same update statement), plus verification:

update
  ( select t1.col2 as t1_c2, t2.col2 as t2_c2
    from   tab1 t1 join tab2 t2 on t1.col1 = t2.col1
  )
set t1_c2 = t2_c2; 

1 row updated.

select * from tab1;

      COL1       COL2
---------- ----------
         1          6

So - you can do it, if you use the correct syntax (as I have shown here) AND - very important - you have a unique or PK constraint or a unique index on column tab2.col1.

CodePudding user response：

I might be missing something, but if your col1 is unique reference it could be as simple as:

UPDATE tab1 t
   SET col2 = (SELECT col2
                 FROM tab2
                WHERE col1 = t.col1)
 WHERE col1 IN (SELECT col1 FROM tab2);