Imagine you’ve been asked to write a script. This must update every employee’s salary to the maximum salary for their job as part of an employee recognition program.

One approach is to break it into two steps:

  1. Query a job to find its maximum salary
  2. Update the salary for all the employees with this job to the value found

You’ll need to repeat this process for every job. This means either:

  • Looping through the jobs and running the update. This is slow.
  • Using bulk processing to get the job details and change the corresponding employee rows. This is fiddly.

It’s more efficient and straightforward to change everyone’s salary by running one update. To do this you need to join employees to jobs. Oracle AI Database offers many ways you can do this. Read on to learn how to:

We’ll also dive into the details of using these statements to handle:

Scroll to the end for a cheat sheet summarizing all the options. 

Direct join syntax to update employees salary to the max_salary for their job. Added in release 23ai

Direct joins to update a table with data from another table

Added in Oracle AI Database 26ai, direct joins in update enable you to set values in one table using columns from another by

  • Placing the source table in the from (or using) clause
  • Joining it to the target table in the where clause

The basic syntax for this is:

Using the standard HR schema, you can set everyone’s salary to the maximum for their job with:

The condition emp.salary < job.max_salary means the update only changes salaries for staff who earn below the maximum for their job. This skips staff who already earn the maximum, minimizing statement’s work. If anyone is lucky enough to earn above their pay band, this also avoids decreasing their pay. You’ll need to confirm with your manager whether this is necessary 😉

This syntax also enables you to restrict which rows change. Instead of company-wide pay adjust, say IT staff will get a 10% raise. You can join departments to only change the rows for these people:

What if you want to use values from departments and jobs? For example, to set all IT staff’s pay to the highest for their band and their manager to be the department’s manager?

Update by joining many tables

You can include many source tables in direct updates. Do this by joining them to any of the existing tables in the statement. You can do this using ISO join syntax or an Oracle-style comma-separated list of tables in the from clause with joins in the where clause.

To set all IT staff to earn the greatest for their pay band and their manager to the department head, you need both jobs and departments. But there’s no direct way to join these two tables. You can cross join these tables, linking them both back to employees in the where clause like this:

Note the or condition in this statement. It’s possible some that IT staff already earn the maximum and report to manager of the department. The or ensures you only change people where at least one of these situations applies.

While this does the job, there are drawbacks:

  • If most employees only need either their salary or their manager to change, it may lead to wasted work
  • The cross join makes it unclear what’s going on.

To overcome these, consider writing two separate update statements.

Whether using one statement or two, you may want to capture the original and new salaries, e.g. for debugging and auditing. AI Database extended the returning clause to enable this.

To use this, define a record of the values you want to capture. Here, that’s the employee_ids along with their old and new salaries. Then create an associative array of this record.

To capture the values, use the returning clause with the old and new expressions. These define whether to get the pre- or post-update values respectively. Then bulk collect these into the array of the records:

Direct join syntax provides a simple way to update one table from another. But what if you’re using older releases?

An update-only merge is my next tool to reach for.

Update-only merge to set employees salary to the maximum for their job

Update one table from another with merge

The merge statement enables update-if-exists, insert-if-not-exists logic. Both parts of it are optional, so you can do an update-only merge. This enables you to update one table from another in releases 21c and earlier.

List the target table in the into clause and the source table in the using clause. The on clause defines the join.

You can rewrite the direct update above to set pay equal to each job’s max salary above as a merge like this:

But run it and you’ll get this error:

ORA-38104: Columns referenced in the ON Clause cannot be updated: "SALARY"

The problem is salary appears in the on clause. You could remove it so it always changes every employee. But doing so means it can process unnecessary rows.

To avoid this, you can add a where clause to the update in merge, like so:

This only changes rows that satisfy the where clause.

For releases 21c and before, an update-only merge is a concise way to assign values from one table to another – provided they’re not part of the join criteria.

But what if you do want to update the join columns?

You can achieve this by updating a query.

Updating a join query to set employees salary to the maximum for their job

Update a subquery with a join

In SQL, everything is a table. This means you can use a query as the target of an update.

To do this, write a select statement that identifies the rows you want to change. Then use it instead of a physical table an in update:

The columns you select in the query can appear on either side of the set clause. The statement can still only change rows in one physical table at a time. The following will fail because it attempts to change both the jobs and employees tables:

Another way to update a query is to save the select in a view. Then update the view. With a view it’s less clear if you’re trying to change two tables at once, but the limitation to only change one table always applies. For clarity, it’s best to avoid updating views and use the query directly in the update.

A similar restriction applies if you try to change a row many times.

Updating the same row twice

In all the solutions above, the database can only update rows in the target table at most once. Attempting to change the same row many times will raise an error.

This is easily seen by flipping the tables in the salary update. Instead of setting each employee’s salary to their highest possible, set each job’s maximum salary to the salary of each employee with that job.

Many people work in each job, so this tries to change each job many times and thus raises an error:

This exception ensures predictable behaviour.

If the database allowed you to change a row many times, the result would be non-deterministic. For example, say three people with the same job role have different salaries. The final maximum salary for the job would depend on the order in which the database processes the rows.

To avoid this error, ensure the update changes each row at most once. You can do this by changing the source table into a subquery. This must guarantees to return at most one row for each value of the join columns.

In this case, it means finding the highest salary for each job. One way to find this is with the partition by extension to fetch first added in Oracle AI Database 26ai.

This gives a statement like:

Avoiding the ORA-30926 is more challenging in older releases.

Firstly, the subquery requires an extra step to find the first row in each group.

More importantly, release 19c had different behaviour. The database checked whether the update could change a row many times at parse time. This meant the target table had to be key-preserved, i.e. constraints had to guarantee each row could only change at most once.

You ensure this by having a primary key (or unique constraint) on the join columns of the source table. If the source table is a subquery this information can be lost.

For example, translating the query to find the max salary for each job into 19c syntax yields:

The subquery is guaranteed to return at most one row per job. The database is unable to verify this though. So it rejects the statement.

The solution is to put the subquery on the right-hand side of the set clause and join it to the target table. To ensure you only change jobs that have employees, the where clause for the update must also check an employee with that job exists:

This is the most general form of an update that joins to another table for its values.

From release 21c key-preservation became a runtime check for all available methods. Now, the database only raises an error if it actually updates one row twice.

This is different with merge. It raises an error if it changes the same row many times across all releases.

At this point you may have spotted another way to solve this problem: setting the max_salary to the max ( salary ) for the corresponding job.

Direct join syntax to update the max salary for each job to the highest salary paid to people working that job

Updating a table with aggregate values from another

Setting a value in one table equal to a total from another is a common operation. For example, to set the total paid for orders or invoices to the sum of the items within them.

Sadly, you can’t set a column equal to an aggregated value from the source table in direct updates. For example, this statement will fail:

Again, the solution here is to group in a subquery. Then join the result of it. Here it is in direct join syntax:

You can use this subquery in other forms of update and merge too.

To finish up, we’ll look at one last scenario. Updating every row in the target table, even those with no matching row in the source table. Aka an outer join.

Updating an outer join

Earlier in this post, we updated all IT staff to report directly to the department head. What if you want to run this hierarchy flattening for everyone, including those with no assigned department? This means an outer join from employees to departments.

The simplest way to do this is with Oracle-style outer joins. Add the (+) operator on the columns of the source table:

To use join syntax, place the target table in the update and from clauses and give them suitable aliases. Join them by primary key in the where clause. You can then left join the source table using the alias in the from clause.

For example:

Compared to Oracle outer joins, this is both more code and more work for the database as it has to read the target table twice.

You can also use an outer join in merge or subquery:

Whichever method you use, the outer join sets the rows in the target with no matching source to null. Be sure this is what you want! In this case, it means poor Kimberly with no department also now has no manager:

Note you can only outer join to the source table with direct joins. If you try and outer join to the target table, the database will raise an error:

Summary

  • Direct join syntax is a simple way to change one table’s columns using values from another table. Added in Oracle AI Database 26ai. 
  • In earlier releases, an update-only merge is an effective alternative.
  • If you need to update the join columns, use an update on a subquery.
  • If all else fails, you can use a correlated subquery update.
  • In all cases, an update can only change each row once.

Here’s a cheat sheet outlining these options:

Cheat sheet summarizing the syntax for direct joins, update-only merge, updating a subquery, and correlated subquery updates

UPDATE 20 Oct 2025: Oracle AI Database 26ai replaces Oracle Database 23ai