asked on

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.

slightwv (䄆 Netminder)

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?

rdbconcepts

ASKER

I've never written a MERGE statement unfortunately : /

slightwv (䄆 Netminder)

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.

rdbconcepts

ASKER

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)

>>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.

rdbconcepts

ASKER

Thanks, if you could, that would be helpful.

In the mean time, I shall look into converting the statement myself.

ASKER CERTIFIED SOLUTION

slightwv (䄆 Netminder)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

rdbconcepts

ASKER

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)

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

rdbconcepts

ASKER

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)

>>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

Peter Chan

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.

rdbconcepts

ASKER

Hi

There are no FK constraints present, in either of the tables.

Peter Chan

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)

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.

rdbconcepts

ASKER

The solution was to rewrite the query as a MERGE statement, as described.