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

From Oracle database 23ai onward we are allowed to use direct joins to tables to drive UPDATE and DELETE statements.

◉ Setup

The following tables are necessary to run the examples in this article.

drop table if exists t1 purge;

drop table if exists t2 purge;

drop table if exists t3 purge;

create table t1 as

select level as id,

       'CODE' || level as code,

       'Description for ' || level as description

from   dual

connect by level <= 100;

alter table t1 add constraint t1_pk primary key (id);

create table t2 as

select level as id,

       'CODE' || (level*10) as code,

       'Updated description for ' || (level*10) as description

from   dual

connect by level <= 100;

alter table t2 add constraint t2_pk primary key (id);

create table t3 as

select level as id,

       'CODE' || (level*10) as code,

       'Updated description for ' || (level*10) as description

from   dual

connect by level <= 100;

alter table t3 add constraint t3_pk primary key (id);

◉ Direct Joins for UPDATE

First we check the data for the first five rows.

column code format a10

column description format a30

select * from t1 where id <= 5;

        ID CODE       DESCRIPTION

---------- ---------- ------------------------------

         1 CODE1      Description for 1

         2 CODE2      Description for 2

         3 CODE3      Description for 3

         4 CODE4      Description for 4

         5 CODE5      Description for 5

SQL>

Now we update the data in T1 using a join to the T2 table. We want to update the T1.CODE and T1.DESCRIPTION values, using the values from T2.CODE and T2.DESCRIPTION using a join in the ID value.

update t1 a 

set    a.code        = b.code,

       a.description = b.description

from   t2 b

where  a.id = b.id

and    b.id <= 5; 

Now we see the T1.CODE and T1.DESCRIPTION values have been updated.

select * from t1 where id <= 5;

        ID CODE       DESCRIPTION

---------- ---------- ------------------------------

         1 CODE10     Updated description for 10

         2 CODE20     Updated description for 20

         3 CODE30     Updated description for 30

         4 CODE40     Updated description for 40

         5 CODE50     Updated description for 50

SQL>

Let's rollback the changes.

rollback;

We can't use the ANSI join syntax between T1 and T2, but if there were several tables driving the update, those could be joined together using ANSI joins. The following example is a bit silly, but it proves a point by joining T2 to T3.

update t1 a 

set    a.code        = b.code,

       a.description = b.description

from   t2 b

join   t3 c on b.id = c.id

where  a.id = b.id

and    b.id <= 5;

rollback;

◉ Direct Joins for DELETE

First we check the data for the first five rows.

column code format a10

column description format a30

select * from t1 where id <= 5;

        ID CODE       DESCRIPTION

-------- ---------- ------------------------------

         1 CODE1      Description for 1

         2 CODE2      Description for 2

         3 CODE3      Description for 3

         4 CODE4      Description for 4

         5 CODE5      Description for 5

SQL>

We delete rows from T1 based on a query from T2. Notice we have a join between the two tables using the ID column, and one or more predicates to determine which rows from T2 are being used to drive the delete.

delete t1 a 

from   t2 b

where  a.id = b.id

and    b.id <= 5; 

We can see the rows have been deleted.

select * from t1 where id <= 5;

no rows selected

SQL>

Let's rollback the changes.

rollback;

We can add in the FROM keyword after the DELETE keyword, but it doesn't scan well.

delete from t1 a 

from   t2 b

where  a.id = b.id

and    b.id <= 5;

rollback;

We can't use the ANSI join syntax between T1 and T2, but if there were several tables driving the delete, those could be joined together using ANSI joins. The following example is a bit silly, but it proves a point by joining T2 to T3.

delete t1 a 

from   t2 b

join   t3 c on b.id = c.id

where  a.id = b.id

and    b.id <= 5;

rollback;

Source: oracle-base.com