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

One of the most elegant SQL enhancements in Oracle Database 23ai (continued in 26ai) is the support for direct joins in UPDATE and DELETE statements. This syntax lets you perform DML that references other tables using a FROM clause—without resorting to nested subqueries or EXISTS.

The new syntax

UPDATE with direct join

UPDATE target_table alias
   SET column = expression [, column = expression, ...]
   FROM join_table jt1 [JOIN jt2 ON ...]
  WHERE join_condition;

DELETE with direct join

DELETE target_table alias
  FROM join_table jt1 [JOIN jt2 ON ...]
 WHERE join_condition;

Oracle determines the target of the DML by the table that follows the UPDATE or DELETE keyword.

Let’s set up two small tables:

CREATE TABLE t1 (id NUMBER, code VARCHAR2(20), description VARCHAR2(100));

CREATE TABLE t2 (id NUMBER, code VARCHAR2(20), description VARCHAR2(100));

INSERT INTO t1 VALUES (1, 'A1', 'Old');
INSERT INTO t1 VALUES (2, 'A2', 'Old');
INSERT INTO t2 VALUES (1, 'A1_new', 'Updated');
INSERT INTO t2 VALUES (2, 'A2_new', 'Updated');

COMMIT;

Direct-join UPDATE:

UPDATE t1 a
   SET a.code        = b.code,
       a.description = b.description
   FROM t2 b
  WHERE a.id = b.id;

Result: Rows in t1 are updated using values from t2 where id matches—clear and expressive.

Example: DELETE with direct join

Traditional form (pre-23c) often looked like this:

DELETE FROM t1
 WHERE id IN (SELECT id FROM t2 WHERE code = 'Updated');

Direct-join DELETE in 23ai/26ai:

DELETE t1 a
  FROM t2 b
 WHERE a.id = b.id
   AND b.code= 'Updated';

This mirrors how we think about data relationships: “delete from t1 where it joins with t2 on some condition”.

Bottom line: Direct joins for UPDATE and DELETE won’t magically speed up your SQL, but they will make it clearer, safer, and far more maintainable. For complex, multi-table operations, this is a welcome upgrade.

Have fun

  Tomasz