• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 460
  • Last Modified:

help with Oracle MERGE statement

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
Dave Ford
Asked:
Dave Ford
  • 2
2 Solutions
 
slightwv (䄆 Netminder) Commented:
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
 
slightwv (䄆 Netminder) Commented:
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
 
awking00Commented:
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
 
Dave FordSoftware Developer / Database AdministratorAuthor Commented:
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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now