flscooter
asked on
Using Merge, updating multiple columns from another table, but missing unique key
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
zip-code.xls
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.
zip-code.xls
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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
update.txt