UPDATE with JOIN syntax for Oracle Database

Question

First, I execute the following SQL statements.

drop table names;
drop table ages;

create table names (id number, name varchar2(20));
insert into names values (1, 'Harry');
insert into names values (2, 'Sally');
insert into names values (3, 'Barry');

create table ages (id number, age number);
insert into ages values (1, 25);
insert into ages values (2, 30);
insert into ages values (3, 35);

select * from names;
select * from ages;

As a result, the following tables are created.

        ID NAME
---------- ----------
         1 Harry
         2 Sally
         3 Barry

        ID        AGE
---------- ----------
         1         25
         2         30
         3         35

Now, I want to update increment the age of Sally by 1, i.e. set it to 31. The following query works fine.

update ages set age = age + 1 where id = (select id from names where name = 'Sally');
select * from ages;

The table now looks like this.

        ID        AGE
---------- ----------
         1         25
         2         31
         3         35

I want to know if there is a way it can be done by joins. For example, I tried the following queries but they fail.

SQL> update ages set age = age + 1 from ages, names where ages.id = names.id and names.name = 'Sally';
update ages set age = age + 1 from ages, names where ages.id = names.id and names.name = 'Sally'
                              *
ERROR at line 1:
ORA-00933: SQL command not properly ended


SQL> update ages set age = age + 1 from names join ages on ages.id = names.id where names.name = 'Sally';
update ages set age = age + 1 from names join ages on ages.id = names.id where names.name = 'Sally'
                              *
ERROR at line 1:
ORA-00933: SQL command not properly ended

Solution

The syntax of the UPDATE statement is:

http://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_10007.htm

where dml_table_expression_clause is:

Please pay attention on ( subquery ) part of the above syntax.

The subquery is a feature that allows to perform an update of joins.

In the most simplest form it can be:

UPDATE (
   subquery-with-a-join
)
SET cola=colb

Before update a join, you must know restrictions listed here:

https://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_8004.htm

The view must not contain any of the following constructs:

• A set operator
• A DISTINCT operator
• An aggregate or analytic function
• A GROUP BY, ORDER BY, MODEL, CONNECT BY, or START WITH clause
• A collection expression in a SELECT list
• A subquery in a SELECT list
• A subquery designated WITH READ ONLY
• Joins, with some exceptions, as documented in Oracle Database Administrator's Guide

and also common rules related to updatable views - here (section: Updating a Join View):
http://docs.oracle.com/cd/B19306_01/server.102/b14231/views.htm#sthref3055

All updatable columns of a join view must map to columns of a key-preserved table. See "Key-Preserved Tables" for a discussion of key-preserved tables. If the view is defined with the WITH CHECK OPTION clause, then all join columns and all columns of repeated tables are not updatable.

We can first create a subquery with a join:

SELECT age 
FROM ages a
JOIN names m ON a.id = m.id
WHERE m.name = 'Sally'

This query simply returns the following result:

       AGE
----------
        30

and now we can try to update our query:

UPDATE (
    SELECT age 
    FROM ages a
    JOIN names m ON a.id = m.id
    WHERE m.name = 'Sally'
)
SET age = age + 1;

but we get an error:

SQL Error: ORA-01779:cannot modify a column which maps to a non key-preserved table

This error means, that one of the above restriction is not meet (key-preserved table).

However if we add primary keys to our tables:

alter table names add primary key( id );
alter table ages add primary key( id );

then now the update works without any error and a final outcome is:

select * from ages;

        ID        AGE
---------- ----------
         1         25
         2         31
         3         35

Comments

[a_horse_with_no_name]

Where in the Oracle manual did you find that syntax?

[a_horse_with_no_name]

stackoverflow.com/search?q=[oracle]+update+join

[mathguy]

@Ben (who marked this as an exact duplicate) - did you read the other, older question? This is not a duplicate, much less an exact one. In the old question the join was used to find the updated value for the update statement. In this one it is used to identify which rows to update. The overlap between the correct solutions for the two problems is probably close to zero. Do you care to elaborate?

[Lone Learner]

@Ben Why is this marked as duplicate? Doesn't it require at least 5 votes to mark it as duplicate?

[Scholar]

Thank you for this well-constructed answer.