1

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.

Raghu
  • 13
  • 5

1 Answers1

0

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

result

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;
F.Lazarescu
  • 1,385
  • 2
  • 16
  • 31
  • I'll try it out, but can you please tell whether it will consider not only the records that are present in the table already but also the records that get updated as a part of this MERGE and provide the next occurrence accordingly? I doubt. – Raghu Aug 09 '19 at 11:19
  • Hi, yes, you are right: it does not take in consideration the records that will be updated as part of this merge. I don't understand why do you need that .. how the result will be changed if we can do that .. – F.Lazarescu Aug 09 '19 at 11:23
  • Please check the expected and actual table given above. With my SQL and your SQL, second record will not be updated with 3 as occurrence. – Raghu Aug 09 '19 at 11:46
  • @Raghu, I just changed my answer, please see it now. It returns your desired output. – F.Lazarescu Aug 09 '19 at 12:03
  • But which portion of your SQL consider the records we update as a part MERGE? If I am right, the SELECT portion you mentioned will be executed only once at the beginning and the update will happen based on the number of record the SELECT gives, thus we always get '2'. Correct me If I am wrong. – Raghu Aug 09 '19 at 12:06
  • 1
    Just noticed your update, I'll try it out and let you know on Monday as am traveling and don't have access to work. Thanks for your effort. – Raghu Aug 09 '19 at 12:10
  • Your supposition it's partially true. Why 2? you have 4 rows there .. i'm getting dizzy :) – F.Lazarescu Aug 09 '19 at 12:11
  • I would like to update only the records whose parent is 'A', to 'B'. Don't get dizzy. It is just a business requirement :) – Raghu Aug 09 '19 at 12:17
  • So I think that a simple where parent = A, will select only 3 records from 4. It's correct like this? – F.Lazarescu Aug 09 '19 at 12:20
  • Not a business requirement, but a situation. The application I am working on now has some pretty old tables with parent, child and occurrence columns all together defined as unique. We cannot change the constraint. Hence we need to update the occurrence based on the combination of parent and child to keep them unique. – Raghu Aug 09 '19 at 12:27
  • 1
    Yes, 3 record needs to be updated with 'B' as parent and the occurrence should be taken care as mentioned – Raghu Aug 09 '19 at 12:28
  • Use of `MERGE` is business requirement? – Popeye Aug 10 '19 at 05:06
  • 1
    Thank you, Lazarescu!. It helped my scenario. – Raghu Aug 12 '19 at 08:13