Solved

help with Oracle MERGE statement

Posted on 2015-02-20
4
389 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

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Create table from select - oracle 6 38
SQL Server 2012 r2 Make faster Temp Table 17 103
Help on model clause 5 30
TSQL query to generate xml 4 32
How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

786 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