I have a table T1 with columns ( C1,C2,C3).
I've written say a complex SELECT query that returns 3 fields say S1, S2, S3
of the same data type as that of C1, C2, C3.
What I want is to update the table T1's C3 value with S3 where C1 = S1 and
C2 = S2. how do I connect the UPDATE statement with the Select statement to
achieve the desired result.

something like

UPDATE T1 Set C3 = S3
WHERE .......(select query goes here )

thx.

What you want is a coordinated sub-query, that is, where a.c1 = b.c1
and a.c2 = b.c2 then update a.c3 to equal b.c3

update T1 A
set C3 = (select b.c3 from T2 B where b.c1 = a.c1 and b.c2 = a.c2)
where exists ( select 'X' from T2 C
where c.c1 = a.c1 and c.c2 = a.c2 )

the EXISTS is to prevent updating C3 to null where no matching row
exists in T2.

HTH -- Mark D Powell --

"Mansoor Azam" <mansoorb (AT) shoa (DOT) net> wrote

Look up correlated subqueries.

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.