Update a table from selecting another table
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: Update a table from selecting another table

  1. #1
    Join Date
    Mar 2001
    Posts
    286
    I would like to update emp table by selecting data from a source table temp0430.

    I use this SQL statement:

    update emp x
    set sal = (select sal from temp0430 y where y.empno=x.empno)
    where empno in (select empno from temp0430);

    Is this correct? Is there any better way?

    SQL> select * from emp

    EMPNO ENAME SAL
    ---------- ---------- -----
    7369 SMITH 8888
    7499 ALLEN 8888
    7521 WARD 8888
    7566 JONES 8888
    7654 MARTIN 8888
    7698 BLAKE 8888
    7782 CLARK 8888
    7788 SCOTT 8888
    7839 KING 8888
    7844 TURNER 8888
    7876 ADAMS 8888
    7900 JAMES 8888
    7902 FORD 8888
    7934 MILLER 8888

    14 rows selected.

    SQL> select * from temp0430

    EMPNO SAL
    ---------- ----------
    7566 2975
    7698 2850
    7788 3000
    7839 5000
    7902 3000

    SQL> update emp x
    2 set sal = (select sal from temp0430 y where y.empno=x.empno)
    3 where empno in (select empno from temp0430);

    5 rows updated.

    SQL> select * from emp;

    EMPNO ENAME SAL
    ---------- ---------- -------
    7369 SMITH 8888
    7499 ALLEN 8888
    7521 WARD 8888
    7566 JONES 2975
    7654 MARTIN 8888
    7698 BLAKE 2850
    7782 CLARK 8888
    7788 SCOTT 3000
    7839 KING 5000
    7844 TURNER 8888
    7876 ADAMS 8888
    7900 JAMES 8888
    7902 FORD 3000
    7934 MILLER 8888

    14 rows selected.

    SQL> commit;
    Share on Google+

  2. #2
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Instead of the IN clause, you can:
    update emp x
    set sal = (select sal from temp0430 y where y.empno=x.empno)
    where x.empno = y.empno
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."
    Share on Google+

  3. #3
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    Sorry Jeff, but that one won't work. Y is un-defined at the outer level - I simply couldn't resist pointing out your mistake - you make so dang few of them .

    But now that I've done that, I suppose I should give dba_admin some help .

    Okay, here are some options - you will probably want to test each one and get the stats - each solution is applicable under different circumstances:

    I've changed the table and column names to stuff from my database, and I'm feeling too lazy to change them back and format the statments, so you'll have to do that yourself

    update org x
    set org_dba_nm = (select org_dba_nm from org y where y.org_pk=x.org_pk)
    where exists (select 1 from org y where y.org_pk=x.org_pk)

    update
    (select x.org_pk, x.org_dba_nm, y.org_dba_nm as nm2
    from org x, org y
    where
    y.org_pk=x.org_pk)
    set
    org_dba_nm = nm2

    update org x
    set org_dba_nm = (select nvl(y.org_dba_nm, x.org_dba_nm) from org y where y.org_pk(+)=x.org_pk)

    ...together with your original solution gives you a total of 4 different solutions to the same problem. Each has its own usefulness based on such things as table size, indexes, etc.

    HTH,

    - Chris
    Share on Google+

  4. #4
    Join Date
    Mar 2000
    Location
    Chennai.Tamilnadu.India.
    Posts
    658

    Exists

    Hi DBA_ADMIN, 2nd May 2001 19:20 hrs chennai

    As Chrislong has said to Use EXISTS is a good option with avoids the full table scan.There is by increasing the performance.

    Cheers

    Padmam
    Attitude:Attack every problem with enthusiasam ...as if your survival depends upon it
    Share on Google+

  5. #5
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Originally posted by chrisrlong
    Sorry Jeff, but that one won't work. Y is un-defined at the outer level
    I stand corrected.
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."
    Share on Google+

  6. #6
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    That's okay - you're still *the man*

    - Chris
    Share on Google+

  7. #7
    Join Date
    Mar 2001
    Posts
    286
    Thank you all of you!

    Share on Google+

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width