Update records in one table based on values of another table in oracle database

drop table emp_halim;

CREATE TABLE emp_halim AS SELECT * FROM emp;

SET DEFINE OFF;
Insert into EMP_HALIM
(EMPNO, ENAME, JOB, MGR, HIREDATE,
SAL, COMM, DEPTNO)
Values
(7999, 'halim', 'CLERK', 7782, TO_DATE('01/23/1982 00:00:00', 'MM/DD/YYYY HH24:MI:SS'),
1300, NULL, 70);
Insert into EMP_HALIM
(EMPNO, ENAME, JOB, MGR, HIREDATE,
SAL, COMM, DEPTNO)
Values
(7989, 'halim', 'CLERK', 7782, TO_DATE('01/23/1982 00:00:00', 'MM/DD/YYYY HH24:MI:SS'),
1300, NULL, 60);
Insert into EMP_HALIM
(EMPNO, ENAME, JOB, MGR, HIREDATE,
SAL, COMM, DEPTNO)
Values
(7979, 'halim', 'CLERK', 7782, TO_DATE('01/23/1982 00:00:00', 'MM/DD/YYYY HH24:MI:SS'),
1300, NULL, 50);
COMMIT;

SELECT *
FROM emp_halim;

----------------------------------------------

drop table dept_halim;

CREATE TABLE dept_halim AS SELECT * FROM dept;

SELECT *
FROM dept_halim;

UPDATE emp_halim a
SET ename = (SELECT dname
FROM dept_halim b
WHERE a.deptno = b.deptno)

rollback ;

UPDATE emp_halim a
SET ename = (SELECT dname
FROM dept_halim b
WHERE a.deptno = b.deptno)
WHERE EXISTS (SELECT dname
FROM dept_halim b
WHERE a.deptno = b.deptno);

rollback;





Live examples:--


SQL*Plus: Release 10.2.0.1.0 - Production on Mon Jul 12 18:27:34 2010

Copyright (c) 1982, 2005, Oracle. All rights reserved.

SQL> conn scott@test107
Enter password:
Connected.
SQL>
SQL>
SQL> set linesize 1000
SQL> set pagesize 1000
SQL>
SQL> UPDATE emp_halim a
2 SET ename = (SELECT dname
3 FROM dept_halim b
4 /
FROM dept_halim b
*
ERROR at line 3:
ORA-00907: missing right parenthesis

SQL>
SQL> drop table emp_halim;

Table dropped.

SQL>
SQL>
SQL> CREATE TABLE emp_halim AS SELECT * FROM emp;

Table created.

SQL>
SQL>
SQL> SET DEFINE OFF;
SQL> Insert into EMP_HALIM
2 (EMPNO, ENAME, JOB, MGR, HIREDATE,
3 SAL, COMM, DEPTNO)
4 Values
5 (7999, 'halim', 'CLERK', 7782, TO_DATE('01/23/1982 00:00:00', 'MM/DD/YYYY HH24:MI:SS'),
6 1300, NULL, 70);

1 row created.

SQL> Insert into EMP_HALIM
2 (EMPNO, ENAME, JOB, MGR, HIREDATE,
3 SAL, COMM, DEPTNO)
4 Values
5 (7989, 'halim', 'CLERK', 7782, TO_DATE('01/23/1982 00:00:00', 'MM/DD/YYYY HH24:MI:SS'),
6 1300, NULL, 60);

1 row created.

SQL> Insert into EMP_HALIM
2 (EMPNO, ENAME, JOB, MGR, HIREDATE,
3 SAL, COMM, DEPTNO)
4 Values
5 (7979, 'halim', 'CLERK', 7782, TO_DATE('01/23/1982 00:00:00', 'MM/DD/YYYY HH24:MI:SS'),
6 1300, NULL, 50);

1 row created.

SQL> COMMIT;

Commit complete.

SQL>
SQL>
SQL> SELECT *
2 FROM emp_halim;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 1000 20
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7876 ADAMS CLERK 7788 23-MAY-87 1100 20
7900 JAMES CLERK 7698 03-DEC-81 950 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
7999 halim CLERK 7782 23-JAN-82 1300 70
7989 halim CLERK 7782 23-JAN-82 1300 60
7979 halim CLERK 7782 23-JAN-82 1300 50

16 rows selected.

SQL>
SQL>
SQL>
SQL>
SQL>
SQL>
SQL> drop table dept_halim;

Table dropped.

SQL>
SQL>
SQL> CREATE TABLE dept_halim AS SELECT * FROM dept;

Table created.

SQL>
SQL> SELECT *
2 FROM dept_halim;

DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON

SQL>
SQL>
SQL>
SQL> UPDATE emp_halim a
2 SET ename = (SELECT dname
3 FROM dept_halim b
4 WHERE a.deptno = b.deptno);

16 rows updated.

SQL>
SQL> rollback ;

Rollback complete.

SQL>
SQL>
SQL>
SQL> UPDATE emp_halim a
2 SET ename = (SELECT dname
3 FROM dept_halim b
4 WHERE a.deptno = b.deptno)
5 WHERE EXISTS (SELECT dname
6 FROM dept_halim b
7 WHERE a.deptno = b.deptno);

13 rows updated.

SQL>
SQL>
SQL>
SQL>
SQL>