This discussion is archived
5 Replies Latest reply: Dec 13, 2012 3:52 AM by Stew Ashton RSS

update statement

Chanchal Wankhade Journeyer
Currently Being Moderated
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 Expert
    Currently Being Moderated
    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-Consulting-com Expert
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points