Wednesday, June 2, 2010

How to write oracle update query without using sub queries

update table3 t3
set (setvaluefield1,setvaluefield2,setvaluefield3) =
(select some_otherfield_from_t2, some2_otherfield_from_t2, '201004' from table2
where exists (select t2.pk_field1,t2.field2,t2.field3
from table1 t1,
table2 t2,
table3 t3
where t3.pk_field1 = t1.pk_field1
and t1.pk_field1 = t2.pk_field1
and t3.field2 = t1.field2
and t1.field2 = t2.field2
and (t3.start_date <= '200808' or t3.t1_init_date = '200808') and (t3.end_pricing_date > '200808'
and t3.t1_end_date >= '200808')
and t1.field3 = t2.field3
and t1.field3 not in ('00',' ')
and t1.field4 = '200808'
and t2.field4 = '200808'
and t2.field5 = 'Y'));




No comments:

Post a Comment