How to update a table using inner join

Question

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;

score 3 · Accepted Answer · edited May 23 '17 at 09:59

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.

So, what did you choose among those three and which one did you prefer? — Benoit, Feb 07 '12 at 11:38

Chip · Answer 2 · 2012-02-07T11:32:04.007

2

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
         )

edited Feb 07 '12 at 11:32

answered Feb 07 '12 at 11:09

Chip

3,226
23
31

Yes. But will fail if an employee cannot be found in dept. – Benoit Feb 07 '12 at 11:17
1

`UNION ALL select null from dual order by 1` should include `NULLS LAST`, but even simpler is `RIGHT JOIN dual ON 1 = 1` – Benoit Feb 07 '12 at 11:40

How to update a table using inner join

2 Answers2