This content has been marked as final. Show 5 replies
Chanchal Wankhade wrote:No.
is the above code is correct.
i haven't execute because i dont want to mess with the data.You'll get a too many rows exception.
you need to find a filed in both tables to do a unique join.
This kind of redundancy is no good. Take a close look at your data model if there is some "natural" relation between those tables than can be expressed in relational relationship...
update branch_master a set a.brchzone = ( SELECT b.brchzone from branch_zone
where a.brchzone is null;
MERGE INTO branch_master a
using branch_zone b
on (a.brchcode = b.brchcode
and a.brchzone is null)
WHEN MATCHED THEN
SET a.brchzone = b.brchzone ;
Edited by: LPS on 13-Dec-2012 03:53
SQL> desc emp
Name Null? Type
EMPNO NOT NULL NUMBER(4)
SQL> desc dept
Name Null? Type
DEPTNO NOT NULL NUMBER(2)
SQL> alter table emp
2 add (dname varchar2(14));
SQL> update emp e
2 set e.dname = (select d.dname from dept d where d.deptno = e.deptno)
3 where e.comm is not null;
4 rows updated.
Edited by: hitgon on Dec 13, 2012 5:22 PM
If you have a primary key or unique constraint on branch_zone.brchcode, you can "update a join":
If you don't, you can use MERGE:
update ( select a.brchzone, b.brchzone new_zone from branch_master a, branch_zone b where a.brchcode=b.brchcode and a.brchzone is null ) set brchzone = new_zone;
Please note that MERGE will produce a runtime error if branch_zone.brchcode is not effectively unique.
merge into branch_master o using ( select a.brchcode, b.brchzone from branch_master a, branch_zone b where a.brchcode=b.brchcode and a.brchzone is null ) n on (o.brchcode=o.brchcode) when matched then update set brchzone = n.brchzone;