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

I need to use the Oracle MERGE statement to update records of the table. One of the column I would like to update should be derived from MAX value of both existing records of the table and the records that are updated as a part of the current MERGE statement. How do i do this?

while updating, I tried max(column value) as inner query, but it didn’t consider the records that are updated as a part of MERGE.

Below is the sample table.

Occurrence indicates the number of combination of parent and child we have.

TABLE NAME: Structure

PARENT     CHILD    OCCURRENCE
A          M        1
A          M        2
A          F        1
B          M        1

Please note that I have mentioned only a few columns and records of this table to explain the requirement. I would like to update the parent ‘A’ to ‘B’. While updating the three records, I have to update the occurrence accordingly. Below is the MERGE statement i tried.

MERGE INTO STRUCTURE a
USING (                                            
      select 'A' as old_parent,
             'B' as new_parent,
             child as child from STRUCTURE
  ) b ON (a.parent = b.old_parent)                                              
  WHEN MATCHED THEN
      UPDATE SET parent = b.new_parent,
                 occurrence = (SELECT NVL(MAX(occurrence)) + 1 
                                  FROM structure
                                  WHERE parent = b.new_parent and
                                  child = b.child)

Since I have written the max(occurrence) as inner query, thought it would be executed every time for every record, thus occurrence would be incremented for every update record, but it didn’t happen.

Below is the expected result

PARENT     CHILD    OCCURRENCE
B          M        2
B          M        3
B          F        1
B          M        1

Actual result I got

PARENT     CHILD    OCCURRENCE
B          M        2
B          M        2
B          F        1
B          M        1

Any help / guidance on how do I achieve this in single MERGE statement would be appreciated. If not possible, is there any other way I can get this done using single statement in sQL? Thanks.

Answer

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;