Re: update with multi table join

1. add a redundant where exists clause
2. wrap the subquery in an NVL() function, i.e.:

update dept
set
deptno = nvl(
(select deptno from dept where dname = 'not') ,deptno
)
where deptno = 44

btw: does anybody know if the use of a subquery in a function is is documented?

-- 
Mark C. Stock
email mcstock -> enquery(dot)com
www.enquery.com
(888) 512-2048

"Ken Chesak" <datavector_at_hotmail.com> wrote in message
news:3f2f39c4.0311061316.78f840b5_at_posting.google.com...


> When the subquery finds nothing the id_bjn is updated with null.  How

> can I avoid updating records when there is no match is found in the

> subquery?

>

> update w_rg101 w

> set id_bjn = ( select c.id_destination  from bjn a, bjn_destination c

> where w.id_region_tw = a.id_region

> and w.id_unit_tw = a.id_unit

> and w.id_mail_tw = a.id_mail)

> where cd_source <> 'ITSI' and id_bjn is null;