Solved

help with Oracle MERGE statement

Posted on 2015-02-20
4
415 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:Dave Ford
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 77

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 77

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:Dave Ford
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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

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.​
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
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.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

738 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