I'm getting "ORA-01779: cannot modify a column which maps to a non key-preserved table" when I try to update a join. I've searched around the site and found a lot of advice on what key-preserved means and why it is necessary… but as near as I can tell I'm complying with that advice, and still getting the error.
I have two tables:
PG_LABLOCATION has, among other things, the columns:
"LABLOCID" NUMBER,
"DNSNAME" VARCHAR2(200 BYTE)
LABLOCID is the primary key, DNSNAME has a unique constraint
PG_MACHINE has, among other things, the columns:
"MACHINEID" NUMBER,
"LABLOCID" NUMBER,
"IN_USE" NUMBER(1,0) DEFAULT 0,
"UPDATE_TIME" TIMESTAMP (6) DEFAULT '01-JAN-1970'
MACHINEID is a primary key
LABLOCID is a foreign key into LABLOCID in PG_LABLOCATION (its primary key)
The update I'm running is:
update
(select mac.in_use, mac.update_time
from pg_machine mac
inner join pg_lablocation loc
on mac.lablocid = loc.lablocid
where loc.dnsname = 'value'
and '02-JAN-2013' > mac.update_time
)
set in_use = 1 - MOD( 101, 2 ), update_time = '02-JAN-2013';
I'm only updating values in one table (PG_MACHINE) and the join column in the other table is the primary key, which should make it key-preserved by my reading. I'm concerned that the where clause is causing the problem, but I tried removing the filter on mac.update_time and got the same error, and loc.dnsname has a unique constraint.
What's even odder is that we have, like many folks, a dev and a prod environment. We did a complete schema and data migration from prod to dev. I've looked them both over and they have identical indexes and constraints. The query works in dev, but generates the above error in prod.
So two questions:
1) Can you see what's wrong with my query?
2) Can you suggest what might be different between my dev and prod environment (e.g. server settings) that could cause this error in one but not the other?
Best Answer
You can update a join in Oracle if the following conditions are met:
(additional restrictions on updating views apply)
In your example you update table
PG_MACHINE
only. Oracle has to make sure that for a single row of this table, only one row of the other can be found. This seems to be the case since you have a PK onPG_LABLOCATION.LABLOCID
. Therefore you should be able to update the join. See for example this SQLFiddle with a similar setup.In your case you should either:
MERGE
ifPG_LABLOCATION.LABLOCID
is unique for the relevant query.MERGE
is less strict than update with joins and will only return an error if there is actually a duplicate in the result set (whereasUPDATE
will fail if there is the possibility of a duplicate).review your query, since you don't need values from the parent table in the
SELECT
clause, you could rewrite it as a semi-join (that guarantees that no duplicate will be generated):This could be rewritten as:
In this case I would go with the third option: in general you can't update the parent in a parent-child join.