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;

Community · Accepted Answer · 2017-05-23 09:59:19Z

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?

Chip · Accepted Answer · 2012-02-07 11:32:04Z

 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 )

UNION ALL select null from dual order by 1 should include NULLS LAST, but even simpler is RIGHT JOIN dual ON 1 = 1

Collectives™ on Stack Overflow

How to update a table using inner join

2 Answers 2

1 Comment

2 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Linked

Related