Home » SQL & PL/SQL » SQL & PL/SQL » Updating one table with data from other table..
Updating one table with data from other table.. [message #226146] Thu, 22 March 2007 11:57 Go to next message
orausern
Messages: 817
Registered: December 2005
Senior Member
I have two table and want to update value of one table with data of another table...can you please suggest the SQL for it:

Here is the test data:
for example suppose two tables having structure of EMP TABLE, and emp1 table has just 5 rows, where emp has 14 and in emp1 table, the salary column is null and need to be updated with that of emp table's salary value...both tables have primar keys(empno)


SQL> select * from emp1;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO BIRTH_DAT
---------- ---------- --------- ---------- --------- ---------- ---------- ---------- ---------
      7369 SMITH      CLERK           7902 17-DEC-80                               20 01-NOV-66
      7499 ALLEN      SALESMAN        7698 20-FEB-81                   300         30 01-FEB-66
      7521 WARD       SALESMAN        7698 22-FEB-81                   500         30 14-JAN-66
      7566 JONES      MANAGER         7839 02-APR-81                               20 16-OCT-65
      7654 MARTIN     SALESMAN        7698 28-SEP-81                  1400         30 23-APR-65

SQL> select * from emp;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO BIRTH_DAT
---------- ---------- --------- ---------- --------- ---------- ---------- ---------- ---------
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20 01-NOV-66
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30 01-FEB-66
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30 14-JAN-66
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20 16-OCT-65
      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30 23-APR-65
      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30 25-JAN-65
      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10 10-AUG-64
      7788 SCOTT      ANALYST         7566 09-DEC-82       3000                    20 29-JUL-64
      7839 KING       PRESIDENT            17-NOV-81       5000                    10 18-APR-64
      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30 08-APR-64
      7876 ADAMS      CLERK           7788 12-JAN-83       1100                    20 04-FEB-64

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO BIRTH_DAT
---------- ---------- --------- ---------- --------- ---------- ---------- ---------- ---------
      7900 JAMES      CLERK           7698 03-DEC-81        950                    30 18-DEC-63
      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20 14-DEC-63
      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10 11-OCT-63

14 rows selected.


Again , thanks for all the help!
Nirav
Re: Updating one table with data from other table.. [message #226152 is a reply to message #226146] Thu, 22 March 2007 12:15 Go to previous messageGo to next message
orausern
Messages: 817
Registered: December 2005
Senior Member
Got the solution!
Re: Updating one table with data from other table.. [message #226156 is a reply to message #226152] Thu, 22 March 2007 12:32 Go to previous messageGo to next message
ddkdhar
Messages: 68
Registered: February 2007
Member

use merge statement
Re: Updating one table with data from other table.. [message #226160 is a reply to message #226156] Thu, 22 March 2007 12:34 Go to previous messageGo to next message
orausern
Messages: 817
Registered: December 2005
Senior Member
I am on 9i, and there is only update not insert..can we use merge still and is it better than update in that case? can u give an example?

Thanks!
Re: Updating one table with data from other table.. [message #226192 is a reply to message #226160] Thu, 22 March 2007 15:44 Go to previous message
tplank
Messages: 7
Registered: March 2007
Junior Member
Don't know if this is what you are looking for, but...

update emp1 a
  set a.sal = ( select b.sal from emp b where b.empno = a.empno )
Previous Topic: how to i ncrease dbms_ouput buffer
Next Topic: Ways of optimizing codes
Goto Forum:
  


Current Time: Sun Dec 04 00:13:19 CST 2016

Total time taken to generate the page: 0.19603 seconds