5 Replies Latest reply: Dec 13, 2012 5:52 AM by Stew Ashton RSS

    update statement

    Chanchal Wankhade
      Hi all,

      We have oracle 10g R2 on windows.

      i have to update the table branch master from the other table like below.

      I need to update branch_master tables column brchzone based on the branch_zones column brchzone.
      update branch_master a set brchzone=(select brchzone from branch_zone b)
      where a.brchcode=b.brchcode
      and a.brchzone is null
      is the above code is correct. i haven't execute because i dont want to mess with the data.

      Kindly help.
        • 1. Re: update statement
          hitgon
          update branch_master a
          set brchzone=(select brchzone from branch_zone b
          where b.brchcode= a.brchcode)
          where a.brchzone is null;

          check the data using the select statement and if it's okay then you can commit otherwise do the rollback
          • 2. Re: update statement
            TPD-Opitz
            Chanchal Wankhade wrote:
            is the above code is correct.
            No.
            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.

            BTW:
            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...

            bye
            TPD
            • 3. Re: update statement
              LPS
              update branch_master a set a.brchzone = ( SELECT b.brchzone from branch_zone
              where a.brchcode=b.brchcode)
              where a.brchzone is null;

              using Merge


              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
              • 4. Re: update statement
                hitgon
                Example:---------------

                SQL> desc emp
                Name                                      Null?    Type
                EMPNO NOT NULL NUMBER(4)
                ENAME VARCHAR2(10)
                JOB VARCHAR2(9)
                MGR NUMBER(4)
                HIREDATE DATE
                SAL NUMBER(7,2)
                COMM NUMBER(7,2)
                DEPTNO NUMBER(2)

                SQL> desc dept
                Name                                      Null?    Type
                DEPTNO NOT NULL NUMBER(2)
                DNAME VARCHAR2(14)
                LOC VARCHAR2(13)


                SQL> alter table emp
                2 add (dname varchar2(14));

                Table altered.

                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
                • 5. Re: update statement
                  Stew Ashton
                  If you have a primary key or unique constraint on branch_zone.brchcode, you can "update a join":
                  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;
                  If you don't, you can use MERGE:
                  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;
                  Please note that MERGE will produce a runtime error if branch_zone.brchcode is not effectively unique.