Solved

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

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

Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Query - Database name 'TempDB' ignored, referencing object in tempdb - Error 2 35
Database Design Dilemma 6 58
average of calculation (TSQL) 4 26
SQL Syntax 14 35
"Disruption" is the most feared word for C-level executives these days. They agonize over their industry being disturbed by another player - most likely by startups.
Get to know the ins and outs of building a web-based ERP system for your enterprise. Development timeline, technology, and costs outlined.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to take different types of Oracle backups using RMAN.

830 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