?
Solved

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

Posted on 2009-06-30
3
Medium Priority
?
595 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 2000 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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

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.

Question has a verified solution.

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

For cloud, the “train has left the station” and in the Microsoft ERP & CRM world, that means the next generation of enterprise software from Microsoft is here: Dynamics 365 is Microsoft’s new integrated business solution that unifies CRM and ERP fun…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
Via a live example, show how to take different types of Oracle backups using RMAN.
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.
Suggested Courses

752 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