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
Leave a Reply