Solved

help with Oracle MERGE statement

Posted on 2015-02-20
4
380 Views
Last Modified: 2015-02-23
I inherited an Oracle script running on Oracle 11g that contains the following MERGE statement:

MERGE INTO MainTable PR
     USING IncrementalTable PRA
       ON (PR.contract = PRA.contract
       and PRA.memberType = 'Parent' )
     WHEN MATCHED THEN
       UPDATE SET PR.address1 = PRA.address1,
                  PR.city = PRA.city,
                  PR.state = PRA.sate,
                  PR.zipCode = PRA.zipCode,
                  PR.countryCode = PRA.countryCode
WHERE address1 IS NULL
  AND PR.memberType <> 'Parent'
;

Open in new window


As far as I can see, this is simply updating the child's address in MainTable from the parent's address in IncrementalTable. Unfortunately, when I run the statement, it throws the following error:

ORA-30926: unable to get a stable set of rows in the source tables

So, it looks like it can't find a distinct match in IncrementalTable with which to update. Querying the data, that appears to be the case:

select contract,
       memberType,
       count(*)
  from IncrementalTable
 group by contract,
       memberType
having count(*) > 1
;

CONTRACT               MEMBERTYPE   COUNT(*)
---------------------- ---------- ----------
1119839490             PARENT              2
7271122516             PARENT              2
1004798721             PARENT              2

Open in new window


Looking at the details of one of those contracts:

select *
  from IncrementalTable
 where contract = '1119839490'
   and memberType = 'Parent'
;


CONTRACT               MEMBERTYPE ADDRESS1          CITY                           STATE ZIPCODE   COUNTRYCODE
---------------------- ---------- ----------------  ------------------------------ ----- -------   -----------
1165439488             Parent     1234 Dorioth St   Orlando                        FL    32825     USA
1165439488             Parent     1234 Dorioth St   Orlando                        FL    32825     USA

Open in new window


So, how can I merge only the DISTINCT match from IncrementalTable?

Thanks in advance for your help!
DaveSlash
0
Comment
Question by:daveslash
  • 2
4 Comments
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40622081
Since you inherited the script, I assume it was designed to run in the current system.

Since you now have duplicates in the data:
Where is the actual problem?

We might be able to 'fix' the script
BUT
Is the actual problem with the duplicate data?

My guess is there shouldn't be duplicates in the data.

Delete the duplicates and the MERGE goes back to running and doesn't need 'fixing'.
0
 
LVL 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 250 total points
ID: 40622095
Another thing to think about:
What if the data wasn't a duplicate?

Say you had:
CONTRACT               MEMBERTYPE ADDRESS1          CITY                           STATE ZIPCODE   COUNTRYCODE
---------------------- ---------- ----------------  ------------------------------ ----- -------   -----------
1165439488             Parent     1234 Dorioth St   Orlando                        FL    32825     USA
1165439488             Parent     1 Main St         Dallas                         TX    12345     USA

Open in new window


Which address should make it into the main table?
0
 
LVL 32

Assisted Solution

by:awking00
awking00 earned 250 total points
ID: 40623147
I have no way of testing this, but you might try -
MERGE INTO MainTable PR
     USING (SELECT distinct contract, address1, city, state, zipCode, countryCode
                  FROM  IncrementalTable
                  WHERE memberType = 'Parent') PRA
       ON (PR.contract = PRA.contract)
     WHEN MATCHED THEN
       UPDATE SET PR.address1 = PRA.address1,
                  PR.city = PRA.city,
                  PR.state = PRA.sate,
                  PR.zipCode = PRA.zipCode,
                  PR.countryCode = PRA.countryCode
WHERE address1 IS NULL
  AND PR.memberType <> 'Parent'
0
 
LVL 18

Author Closing Comment

by:daveslash
ID: 40625978
slightwv: You make a very good point. If the data isn't actually a duplicate, then the script has REAL problems.  I'll see if I can get that data cleaned up.


awking00: Thank you for the help on the syntax. That'll work.

Thanks to both of you!
DaveSlash
0

Featured Post

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
sql join/ assign small # first 10 83
case statement in where clause 5 41
Oracle Pivot Question 8 45
How to get time difference in minutes and seconds only between 2 dates 2 20
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
This video shows how to recover a database from a user managed backup
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now