Solved

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

Posted on 2009-06-30
3
584 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 32

Accepted Solution

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

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

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
Getting TNS:Connect timeout occurred while opening the application 5 60
SQL query bug 3 28
MS SQL Inner Join - Multiple Join Parameters 2 23
Oracle - SQL Parse String 5 20
Exception Handling is in the core of any application that is able to dignify its name. In this article, I'll guide you through the process of writing a DRY (Don't Repeat Yourself) Exception Handling mechanism, using Aspect Oriented Programming.
Get to know the ins and outs of building a web-based ERP system for your enterprise. Development timeline, technology, and costs outlined.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

862 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

25 Experts available now in Live!

Get 1:1 Help Now