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?