DBA Hub

📋Steps in this guide1/3

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

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

oracle 23configurationintermediate
by OracleDba
18 views
1

Setup

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

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
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);
2

Direct Joins for UPDATE

First we check the data for the first five rows. Now we update the data in using a join to the table. We want to update the and values, using the values from and using a join in the value. Now we see the and values have been updated. Let's rollback the changes. We can't use the ANSI join syntax between and , 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 to .

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
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>

update t1 a 
set    a.code        = b.code,
       a.description = b.description
from   t2 b
where  a.id = b.id
and    b.id <= 5;

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>

rollback;

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;
3

Direct Joins for DELETE

First we check the data for the first five rows. We delete rows from based on a query from . Notice we have a join between the two tables using the column, and one or more predicates to determine which rows from are being used to drive the delete. We can see the rows have been deleted. Let's rollback the changes. We can add in the keyword after the keyword, but it doesn't scan well. We can't use the ANSI join syntax between and , 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 to . For more information see: - UPDATE - DELETE - Updates Based on Queries Hope this helps. Regards Tim...

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
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>

delete t1 a 
from   t2 b
where  a.id = b.id
and    b.id <= 5;

select * from t1 where id <= 5;

no rows selected

SQL>

rollback;

delete from t1 a 
from   t2 b
where  a.id = b.id
and    b.id <= 5;


rollback;

delete t1 a 
from   t2 b
join   t3 c on b.id = c.id
where  a.id = b.id
and    b.id <= 5;


rollback;

Comments (0)

Please to add comments

No comments yet. Be the first to comment!