8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux
Direct Joins for UPDATE and DELETE Statements in Oracle Database 23c
From Oracle database 23c onward we are allowed to use direct joins to tables to drive UPDATE
and DELETE
statements.
Related articles.
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;
For more information see:
Hope this helps. Regards Tim...