Re: Complex update query

Here are a few sample of what works and doesn't works, based on the table structures and the use of primary keys...

Create table t1 (c1 number primary key,c2 number,c3 number,c4 date); Create table t2 (c6 number primary key,c7 number,c8 number,c9 date);

Update (Select t1.c3,t2.c8,t2.c9
from t1,t2
where t1.c2=t2.c7 and t1.c4>t2.c9) myt
set myt.c8=myt.c3+20
where myt.c9 between sysdate-8 and sysdate-5; ORA-01779: cannot modify a column which maps to a non key-preserved table

Update (Select t1.c3,t2.c8,t2.c9
from t1,t2
where t1.c2=t2.c6 and t1.c4>t2.c9) myt
set myt.c8=myt.c3+20
where myt.c9 between sysdate-8 and sysdate-5; ORA-01779: cannot modify a column which maps to a non key-preserved table

Update (Select t1.c3,t2.c8,t2.c9
from t1,t2
where t1.c1=t2.c7-1 and t1.c4>t2.c9) myt set myt.c8=myt.c3+20
where myt.c9 between sysdate-8 and sysdate-5; ORA-01779: cannot modify a column which maps to a non key-preserved table

Update (Select t1.c3,t2.c8,t2.c9
from t1,t2
where t1.c1=t2.c7 and t1.c4>t2.c9) myt
set myt.c8=myt.c3+20
where myt.c9 between sysdate-8 and sysdate-5;
... rows updated.

Update (Select t1.c3,t2.c8,t2.c9
from t1,t2
where t1.c1=t2.c6 and t1.c4>t2.c9) myt
set myt.c8=myt.c3+20
where myt.c9 between sysdate-8 and sysdate-5;
... rows updated.

I hope this helps. Received on Fri Dec 17 2004 - 09:12:58 CST