DIRECT JOINS in UPDATE and DELETE statements.

 

 ORACLE 23-C NEW FEATURES

DIRECT JOINS in UPDATE and DELETE statements.

CONTENTS

SNO	CONTENTS	PAGE NUMBER
1	Introduction	3
2	Why do we need this feature	3
3	Update Query with multiple tables	3
4	DELETE with multiple tables	4
5	Conclusion	5

 

1. Introduction

Join the target table in UPDATE and DELETE statements to other tables using the FROM clause. Direct joins make it easier to write SQL to change and delete data.

2. Why do we need this feature

Direct join is not supported in previous versions of Oracle 23C. To join the multiple tables, we must use sub query or IN clause which may leads performance issue.

Let us see this with example:

In order to increase the salaries of the IT department by 10%, a query shall be

This is the typical method for writing the UPDATE statement in a pre-23c Oracle database was using an inner query in the WHERE clause.

 

3. UPDATE with multiple tables in Oracle 23C

In Oracle database 23c we can now use the join between the two tables and the same WHERE clause as the select statement to make the UPDATE easier to write.

 

4. DELETE with multiple tables in Oracle 23C

 

In the same way , sub query should be used to perform a delete from a target table by joining multiple tables.

The query as follows:

 

The same query can be written in Oracle 23C in a simpler form as follows:

 

5. Conclusion

From Oracle Database 23c onward, we can use direct joins to tables to drive UPDATE and DELETE statements and make writing SQLs to change and delete data easier.