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

• Oracle Database Free
• Database 23ai: Feature Highlights
• Installing Oracle Database 23ai Free
• Direct Joins for UPDATE and DELETE Statements in Oracle Database 23ai – Tim Hall