Solved

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

Posted on 2009-06-30
3
592 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
[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
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

Is Your DevOps Pipeline Leaking?

Is your CI/CD pipeline a hodge-podge of randomly connected tools? You’ve likely got a tool to fix one problem & then a different tool to fix another, resulting in a cluster of tools with overlapping functionality. Learn how to optimize your pipeline with Gartner's recommendations

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SP result not being displayed 5 68
Need more granular date groupings 4 44
Creating a View from a CTE 15 45
Convert summed columns to Rows 6 23
I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
These days socially coordinated efforts have turned into a critical requirement for enterprises.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows how to recover a database from a user managed backup

732 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