Skip to content
Advertisement

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.

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.

Advertisement

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:

result

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

User contributions licensed under: CC BY-SA
10 People found this is helpful
Advertisement