Update Multiple Columns in Merge Statement Oracle

How do i update multiple records using MERGE statement and use max(column_value) based on previously updated records in the same statement?

EDIT 3:
I successfully received your desire output, but I don't understand why do you need such result. Please change the from clause and put it in your merge:

SELECT parent old_parent,
       'B'    AS new_parent,
       child  AS child,
       occurence,
         MAX (occurence) OVER (PARTITION BY parent, CHILD ORDER BY 1)
       + COUNT (*) OVER (PARTITION BY parent, child ORDER BY 1)
       - OCCURENCE
           NEW_OCCURENCE
  FROM (SELECT 'a' parent, 'm' child, 1 occurence FROM DUAL
        UNION ALL
        SELECT 'a' parent, 'm' child, 2 occurence FROM DUAL
        UNION ALL
        SELECT 'a' parent, 'f' child, 1 occurence FROM DUAL
        UNION ALL
        SELECT 'b' parent, 'm' child, 1 occurence FROM DUAL) STRUCTURE

I think that you can use the Max as analytical function. See the merge below:

MERGE INTO STRUCTURE a
     USING (SELECT old_parent,
                   'B'   AS new_parent,
                   child AS child,
                   MAX (occurrence) OVER (PARTITION BY old_parent, CHILD ORDER BY 1) + 1
                       NEW_OCCURENCE
              FROM STRUCTURE) b
        ON (a.parent = b.old_parent)
WHEN MATCHED
THEN
    UPDATE SET parent = b.new_parent, occurrence = b.NEW_OCCURENCE;

How could I update multiple columns in Oracle with same id?

merge into tableA a
using tableB b
on (a.name=b.name)
when matched then update set
columnC = b.columnE,
columnH = b.columnF

create table tableA (columnC varchar2(20), columnH varchar2(20), name varchar2(20), columnA number);
create table tableB (columnE varchar2(20), columnF varchar2(20), name varchar2(20));
insert into tableA values (null, null,'Harry',1);
insert into tableA values (null, null,'Harry',3);
insert into tableA values (null, null,'Harry',3);
insert into tableB values ('a', 'd','Harry');
insert into tableB values ('b', 'e','Ron');
insert into tableB values ('c', 'f','Hermione');
select * from tableA;
merge into tableA a
using tableB b
on (a.name=b.name)
when matched then update set
columnC = b.columnE,
columnH = b.columnF;
select * from tableA;

I got no error

Updating multiple columns of a table

MERGE
INTO    table1 t1
USING   table2 t2
ON      (SUBSTR (otname, INSTR (otname,'.', 1, 3)
                         + 1, 
                         INSTR (otname, '.', 1, 4)
                              - INSTR (otname,'.', 1, 3)
                              - 1)
                               = t2.sncode))
WHEN MATCHED THEN
UPDATE
SET    t1.sncode = t2.sncode,
       t1.description_text = t2.description_text

You also can simplify your expression:

MERGE
INTO    table1 t1
USING   table2 t2
ON      (REGEXP_SUBSTR(otname, '[^.]+', 1, 4) = t2.sncode)
WHEN MATCHED THEN
UPDATE
SET    t1.sncode = t2.sncode,
       t1.description_text = t2.description_text

Oracle - conditional merge on multiple columns

This is not exactly what you asked for, but you could restrict the UPDATE to those rows with at least one changed value with a WHERE clause in your UPDATE:

MERGE INTO tbl rs
USING 
  (SELECT res.sid, res.eid, res.a, res.b, res.c, res.d, res.e /* ... */ )
ON ( /* Merge magic here */ )
WHEN MATCHED THEN    
UPDATE SET 
      rs.a = res.a, 
      rs.b = res.b, 
      rs.c = res.c, 
      rs.d = res.d, 
      rs.e = res.e
WHERE rs.a <> res.a 
   OR rs.b <> res.b 
   OR rs.c <> res.c 
   OR rs.d <> res.d 
   OR rs.e <> res.e;

(depending on your data / application logic, you might want to add extra checking for NULL values).

Merge Oracle update both tables

Is it possible to, when matched, not only update columns from table1 but also columns from table2?

No, it is not possible. You can only update a single table using a MERGE statement.

---

Related Topics