Solved

Using Merge, updating multiple columns from another table, but missing unique key

Posted on 2009-06-30
3
580 Views
Last Modified: 2013-12-07
Experts,

I have a temporary table loaded with source data.  I am trying to load htis source data into a table in my DB.  There are three columns that make up the primary key, but my source file only has two of them.  I am loading a zip code table and updating some representative's id's on this table.  The key fields on my main table are zip_code, state, and city.  My source file only has zip and state.  When I asked a senior person in my group about this, they said I should update all reps with that zip code.  I don't think things work this way and I don't want to go back to her without trying everything I can first.  So for example.

My target table:

Zip           State              City         Rep 1              Rep 2
12345        SC               Joe
54321        SC               Flom
34567        SC               Dog
34567        SC               Cat
My source file ( temp table )

Zip            State              City                 Rep 1             Rep 2
12345        SC                                            A                  B
54321        SC                                            C                   D
34567         SC                                            E                   F

I believe I am being asked to update both records in my main table with a zip code of 34567 with reps E and F, for both cities ( records ).  I don't even know if it is possible, but certainly not the way I am doing it.  Any advice would be appreciated.

Scott
merge into tropics_policy.zip_code zc

using tmp_zip_code tzc

on ( zc.zip = tzc.zip_code and zc.state = tzc.state )

when matched then

update

set zc.auditor_id = tzc.auditor,

    zc.customer_service_rep = tzc.csr,

    zc.loss_control_counselor = tzc.lp_rep,

    zc.marketing_rep = tzc.marketing_rep,

    zc.underwriting_rep = tzc.uw
 

Error message I am getting:
 

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

Cause: A stable set of rows could not be got because of a large amount of DML activity or a non-deterministic where clause.  

  

  Action: Remove any non-deterministic where clauses and reissue the DML.

Open in new window

tmp-zip-code.xls
zip-code.xls
0
Comment
Question by:flscooter
  • 2
3 Comments
 
LVL 31

Accepted Solution

by:
awking00 earned 500 total points
ID: 24748422
Use update instead of merge. See attached.
update.txt
0
 
LVL 31

Expert Comment

by:awking00
ID: 24748474
See attached.
update.txt
0
 

Author Closing Comment

by:flscooter
ID: 31598374
awking00,

Thanks for getting back to me.  I found out the merge worked as well, after I deduped the source file.  Thanks again for your time.

Scott
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Note: You must have administrative privileges in order to create/edit Sharing Rules. Salesforce.com (http://www.salesforce.com) (SFDC) is a cloud-based customer relationship management (CRM) system. It is a database most commonly used by sales an…
Read about achieving the basic levels of HRIS security in the workplace.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

747 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

11 Experts available now in Live!

Get 1:1 Help Now