ORA-01779: cannot modify a column which maps to a non key-preserved table

Hi,

We recently updated our Oracle 10g R2 environment to 11g R2 (11.2.0.4), since the upgrade, the following UPDATE statement has been producing the above error:

 update /*+ bypass_ujvc */
(
select s.mm_locationid, s.actualdate, s.time_sk, s.avg_drive_time,s.orders_per_dispatch, s.widgets, s.starters,s.avg_spend, a.drive_time_avg, a.orders_per_dispatch_avg, a.spend_avg, a.widgets_avg, a.starters_avg
from  Del_2014_Labour_Scheduling_app s,del_2014_lab_sch_avg_hours a
where  s.mm_locationid=a.mm_locationid and s.actualdate=a.actualdate
and s.time_sk between a.time_sk_min and a.time_sk_max
/*and s.mm_locationid=9553
and s.actualdate='03-mar-2014'*/
) t
SET t.avg_drive_time = t.drive_time_avg,
t.orders_per_dispatch=t.orders_per_dispatch_avg,
t.widgets=t.widgets_avg,
t.starters=t.starters_avg,
t.avg_spend=t.spend_avg;

I suspect it's the "/*+ bypass_ujvc */" query hint that's causing the issue. Could someone recommend a workaround? The two tables in question currently do not have primary key columns associated, a primary key column has since been created on the base table but the error persists.

Thanks.
rdbconceptsAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) Commented:
Can't help with the update as posted or the hint.  I've never had to use a correlated update.  I've seen a few examples of them.

Whenever I've had the need to do that, I use MERGE:
http://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_9016.htm#SQLRF01606

Can you rewrite the update into a MERGE statement?
rdbconceptsAuthor Commented:
I've never written a MERGE statement unfortunately : /
slightwv (䄆 Netminder) Commented:
If you can write a correlated update, MERGE isn't that that bad.  You just need to separate the query in your update statement into the source select.  Add that to the USING portion.

The destination table is the INTO table.

The join, "ON"  should be the same.  Then add the WHEN MATCHED UPDATE info.

If you can provide a small test case of your tables, sample data and expected results, we can come up the a working MERGE statement.
SolarWinds® IP Control Bundle (IPCB)

Combines SolarWinds IP Address Manager and User Device Tracker to help detect IP conflicts, quickly identify affected systems, and help your team take near instantaneous action. Help improve visibility and enhance reliability with SolarWinds IP Control Bundle.

rdbconceptsAuthor Commented:
Is it not possible to convert my statement that I've posted, into a MERGE statement?. I can test the results myself. I'm under tight time constraints.
slightwv (䄆 Netminder) Commented:
>>Is it not possible to convert my statement that I've posted, into a MERGE statement?.

If I get some time later, I can try to decipher your tables.

>>I'm under tight time constraints.

I suggest you make the attempt.  You understand your tables and columns.  It really isn't difficult.  There is a really good example in the documentation link I provided.
rdbconceptsAuthor Commented:
Thanks, if you could, that would be helpful.

In the mean time, I shall look into converting the statement myself.
slightwv (䄆 Netminder) Commented:
I took a couple of minutes to cobble something together.

Even if it isn't correct, it gives you the basic idea.

merge into Del_2014_Labour_Scheduling_app s
using (select drive_time_avg, orders_per_dispatch_avg, spend_avg, widgets_avg, starters_avg from del_2014_lab_sch_avg_hours) a
on (s.mm_locationid=a.mm_locationid and s.actualdate=a.actualdate and s.time_sk between a.time_sk_min and a.time_sk_max)
when matched then update SET s.avg_drive_time = a.drive_time_avg,
 s.orders_per_dispatch=a.orders_per_dispatch_avg,
 s.widgets=a.widgets_avg,
 s.starters=a.starters_avg,
 s.avg_spend=a.spend_avg;

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
rdbconceptsAuthor Commented:
I pretty much got to where you were, although when I run the MERGE i get an error:

ORA-00904: "A"."TIME_SK_MAX": invalid identifier

I get the same error when I run your suggestion. I'm trying to work out why?
slightwv (䄆 Netminder) Commented:
You cannot reference a column when that column isn't selected.

Just add TIME_SK_MAX to the select (and the min time column):

merge into Del_2014_Labour_Scheduling_app s
using (select time_sk_min, time_sk_max, drive_time_avg, orders_per_dispatch_avg, spend_avg, widgets_avg, starters_avg from del_2014_lab_sch_avg_hours) a
on (s.mm_locationid=a.mm_locationid and s.actualdate=a.actualdate and s.time_sk between a.time_sk_min and a.time_sk_max)
when matched then update SET s.avg_drive_time = a.drive_time_avg,
 s.orders_per_dispatch=a.orders_per_dispatch_avg,
 s.widgets=a.widgets_avg,
 s.starters=a.starters_avg,
 s.avg_spend=a.spend_avg;

Open in new window

rdbconceptsAuthor Commented:
There were a few more corrections that needed to be made, which I did. The code now is:

merge into solb.Del_2014_Labour_Scheduling_app s
using (select mm_locationid, time_sk_min, time_sk_max, actualdate, drive_time_avg, orders_per_dispatch_avg, spend_avg, pizza_avg, starters_avg from solb.del_2014_lab_sch_avg_hours) a
on (s.mm_locationid=a.mm_locationid and s.actualdate=a.actualdate and s.time_sk between a.time_sk_min and a.time_sk_max)
when matched then update SET s.avg_drive_time = a.drive_time_avg,
 s.orders_per_dispatch=a.orders_per_dispatch_avg,
 s.pizza=a.pizza_avg,
 s.starters=a.starters_avg,
 s.avg_spend=a.spend_avg;

Open in new window


Now getting 'ORA-30926: unable to get a stable set of rows in the source tables'! :/
slightwv (䄆 Netminder) Commented:
>>Now getting 'ORA-30926: unable to get a stable set of rows in the source tables'! :/

You have a data issue.  That might be the reason the update you have is generating the ORA-01779.

Your 'ON' clause is causing rows to be returned with different values for the 'SET'.

I went ahead and set up a quick example based on your SQL.

The columns involved in the 'ON' clause have all 1's that match up.  When it goes to set the values, there are two choices:  the 1 or the 2.  Oracle cannot figure out which one you want to use so it gives you the error.

My test case:
--drop table my_2014_Labour_Scheduling_app purge;
--create table my_2014_Labour_Scheduling_app(mm_locationid number, actualdate number, time_sk number, avg_drive_time number, orders_per_dispatch number, pizza number, starters number, avg_spend number);
--
--
--drop table my_2014_lab_sch_avg_hours purge;
--create table my_2014_lab_sch_avg_hours (mm_locationid number, time_sk_min number, time_sk_max number, actualdate number, drive_time_avg number, orders_per_dispatch_avg number, spend_avg number, pizza_avg number, starters_avg number);
--
--insert into my_2014_Labour_Scheduling_app values(1, 1, 1, 1, 1, 1, 1, 1);
--
--insert into  my_2014_lab_sch_avg_hours values(1, 1, 1, 1, 1, 2, 2, 2, 2);
--insert into  my_2014_lab_sch_avg_hours values(1, 1, 1, 1, 1, 1, 1, 1, 1);
--commit;

merge into my_2014_Labour_Scheduling_app s
using (select mm_locationid, time_sk_min, time_sk_max, actualdate, drive_time_avg, orders_per_dispatch_avg, spend_avg, pizza_avg, starters_avg from my_2014_lab_sch_avg_hours) a
on (s.mm_locationid=a.mm_locationid and s.actualdate=a.actualdate and s.time_sk between a.time_sk_min and a.time_sk_max)
when matched then update SET s.avg_drive_time = a.drive_time_avg,
 s.orders_per_dispatch=a.orders_per_dispatch_avg,
 s.pizza=a.pizza_avg,
 s.starters=a.starters_avg,
 s.avg_spend=a.spend_avg;

Open in new window

HuaMin ChenProblem resolverCommented:
Hi,
There can be Foreign key referencing such column, and you have to disable to FK constraint and then you'll be able to adjust its value.
rdbconceptsAuthor Commented:
Hi

There are no FK constraints present, in either of the tables.
HuaMin ChenProblem resolverCommented:
The reason is:    An attempt was made to insert or update columns of a join view which
           map to a non-key-preserved table.
What you should do is:   Modify the underlying base tables directly.
slightwv (䄆 Netminder) Commented:
So the problem was a data issue with your join?

If the fix was to use the EXISTS solution in that link, I would strongly encourage you to reevaluate that as a viable solution.  It hits the second table twice when you do not need to.

The MERGE I suggested hits the table once.  All you need to do is add the appropriate values to the ON clause to make sure you get the correct rows values for the update.
rdbconceptsAuthor Commented:
The solution was to rewrite the query as a MERGE statement, as described.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.