Update With Join Syntax For Oracle Database

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

Solution 1:

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:

altertable names addprimary key( id );
altertable ages addprimary key( id );

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

select*from ages;

        ID        AGE
---------- ----------125231335