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


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.

merge into tropics_policy.zip_code zc
using tmp_zip_code tzc
on ( = tzc.zip_code and zc.state = tzc.state )
when matched then
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

Who is Participating?
awking00Connect With a Mentor Commented:
Use update instead of merge. See attached.
See attached.
flscooterAuthor Commented:

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.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.