Oracle 26ai: UPDATE ... FROM Syntax Tutorial | Update Tables with JOIN (19c vs 26ai)

Oracle 26ai introduces a powerful syntax allowing you to update a table using JOINs. This makes it easy to update multiple rows based on conditions from another table without writing complex subqueries or multiple statements.

Oracle 26ai allows UPDATE ... FROM syntax

To see this in action, check out the YouTube video demonstration below: .

Example Scenario

We have two tables: departments and employees. Initially, the department column in the employees table is empty. We want to populate it based on the dept_name in departments, and later update multiple conditions at once.

Before Update

ID	Name	Dept_ID	Department
1	Alice	10
2	Bob	20
3	Charlie	10
4	David	30
5	Eva	20

After Update

ID	Name	Dept_ID	Department
1	Alice	10	HR
2	Bob	20	IT
3	Charlie	10	HR
4	David	30	Finance
5	Eva	20	IT

Step 1: Create Tables

CREATE TABLE departments(dept_id NUMBER PRIMARY KEY, dept_name VARCHAR2(50), budget NUMBER);

CREATE TABLE employees(id NUMBER PRIMARY KEY, name VARCHAR2(50), dept_id NUMBER, department VARCHAR2(50));

Step 2: Insert Dummy Data

INSERT INTO departments VALUES(10, 'HR', 5000);
INSERT INTO departments VALUES(20, 'IT', 10000);
INSERT INTO departments VALUES(30, 'Finance', 15000);
INSERT INTO employees VALUES(1, 'Alice', 10, NULL);
INSERT INTO employees VALUES(2, 'Bob', 20, NULL);
INSERT INTO employees VALUES(3, 'Charlie', 10, NULL);
INSERT INTO employees VALUES(4, 'David', 30, NULL);
INSERT INTO employees VALUES(5, 'Eva', 20, NULL);

Step 3: Verify Initial Data

SELECT * FROM employees ORDER BY id;

Step 4: Update Employee Departments Using JOIN

Populate the department column in employees from departments table:

UPDATE employees e SET e.department = d.dept_name FROM departments d WHERE e.dept_id = d.dept_id;

Step 5: Verify Updated Data

SELECT * FROM employees ORDER BY id;

Step 6: Update Multiple Conditions at Once

Example: Add "(IT Special)" to IT employees whose name starts with 'B':

UPDATE employees e SET e.department = e.department || ' (IT Special)' FROM departments d WHERE e.dept_id = d.dept_id AND d.dept_name = 'IT' AND e.name LIKE 'B%';

Step 7: Verify Final Data

SELECT * FROM employees ORDER BY id;

SELECT * FROM departments ORDER BY dept_id;

Conclusion

Oracle 26ai’s UPDATE with JOIN capability simplifies updating multiple rows based on conditions from another table. It reduces the need for subqueries, makes SQL cleaner, and improves readability. Additionally, conditional updates allow for fine-grained control. Oracle 19c users will notice that this syntax is not supported and will encounter errors like ORA-00933. The new 26ai feature provides a more powerful, efficient way to manage updates in complex scenarios.

If you found this post helpful, don’t forget to subscribe to my YouTube channel for more tutorials like this, and feel free to share your thoughts or questions in the comments below!