Oracle Database 23ai: Direct Joins for UPDATE and DELETE Statements

Posted on by Leave a comment

Oracle 23ai allows directs joins in update and delete statements. Let us understand how this works.

Lets say we have two tables DEPARTMENT1.

SQL> SELECT * FROM department_1;

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

And DEPARTMENT_2.

SQL> SELECT * FROM department_2;

   DEPTNO DNAME                      LOC
_________ __________________________ ___________
       10 Accounting-10(New York)    NEW YORK
       20 Research-20(Dallas)        DALLAS
       30 Sales-30(Chicago)          CHICAGO
       40 Operations-40(Boston)      BOSTON

The requirement is to update DEPARTMENT_1.DNAME with values in DEPARTMENT_2.DNAME. 23ai onwards, we have the ability to use direct joins in update statements. Hence, we use the following:

SQL> UPDATE department_1 d1
     SET d1.dname = d2.dname
     FROM department_2 d2
     WHERE d1.deptno = d2.deptno;

4 rows updated.

DEPARTMENT_1.DNAME is updated.

SQL> SELECT * FROM department_1;

   DEPTNO DNAME                      LOC
_________ __________________________ ___________ 
       10 Accounting-10(New York)    NEW YORK
       20 Research-20(Dallas)        DALLAS
       30 Sales-30(Chicago)          CHICAGO
       40 Operations-40(Boston)      BOSTON

Now let us see how this works for the DELETE statements. We want to delete all the rows having from DEPARTMENT_2 where the DEPTNO exists in DEPARTMENT_1 table. So, we use:

SQL> DELETE department_2 d2
     FROM department_1 d1
     WHERE d2.deptno = d1.deptno;

4 rows deleted.

And we get.

SQL> SELECT * from department_2;

no rows selected
SQL> roll;
Rollback complete.

You can use the from clause after DELETE keyword.

SQL> DELETE FROM department_2 d2 
     FROM department_1 d1        
     WHERE d2.deptno = d1.deptno;

4 rows deleted.

Note that ANSI join is not supported, but if you have multiple tables driving the table, you can use ANSI joins to join the driving tables.

References

tagged with , , , , , , ,

Leave a comment