CODEWITHSUNDEEP
sqloracle-database
Ulhas Tuscano
Ulhas Tuscano

Reputation: 5620

How to update a table using inner join

I have two tables Emp & Dept. 

SQL> select * from emp where rownum<4;

     EMPNO ENAME      JOB              MGR        SAL     DEPTNO
---------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902        800
      7499 ALLEN      SALESMAN        7698       1600
      7521 WARD       SALESMAN        7698       1250

SQL> select * from dept;

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

i want to update deptno of emp which should be same as deptno of dept table & where empno of dept should be equal to empno of emp; In short i want to update dept using inner join operation with emp;

Upvotes: 2

Views: 1074

Answers (2)

Chip
Chip

Reputation: 3316

Update emp e 
set deptno = ( select DEPTNO from dept d where d.empno = e.empno )

Will work as long as the EMPNO in DEPT is unique.

Edit - As pointed out, this will fail if EMPNO is not there.

The very HACKY fix to this is,

Update emp e

set deptno = (

         SELECT RESULT FROM (
          select DEPTNO RESULT from dept d where d.empno = e.empno
           UNION ALL 
           select NULL from DUAL 
           ORDER BY 1
           ) WHERE ROWNUM < 2
         )

Upvotes: 2

Benoit
Benoit

Reputation: 79165

In Oracle, the canonical way to do it is the MERGE statement:

MERGE INTO emp e
USING dept d
   ON (d.empno = e.empno)
 WHEN MATCHED THEN UPDATE SET e.deptno = d.deptno

However the above will not work if you are using Oracle 9 (see below), or if there are domain index on your tables (Oracle Text for instance)

I asked a question previously about what MERGE statements do.

It is necessary that each row of e be connected by the join condition (ON section) to none or exactly 1 row of d, otherwise you'll get ORA-30926 “Unable to get a stable set of rows in the source tables”.

Rows of e not connected to any department get unchanged, rows of d not connected to any employee could be used for INSERTS (and in Oracle 9 must be used in a WHEN NOT MATCHED THEN INSERT(cols) VALUES(...) clause; if you're using Oracle 9 then this answer is not suitable for you).

Another option, but subject to the same underlying restrictions:

UPDATE (SELECT e.deptno edeptno, d.deptno ddeptno
          FROM emp e, dept d
         WHERE e.empno = d.empno)
   SET edeptno = ddeptno

Or

UPDATE emp
   SET deptno = ( SELECT deptno FROM dept WHERE empno = emp.empno )
 WHERE empno IN ( SELECT empno FROM dept )

As you can see solutions are numerous but subject to constraints.

Upvotes: 3

Related Questions