Update Joins in Oracle

We have an application running on Ms SQL Server, and we want to move it to Oracle. While I examine the custom queries, I see that “update joins”.

UPDATE table_name
SET colname = value
FROM source_table JOIN source_table2
WHERE condition

This syntax helps user to join tables and update together. Oracle does not support this syntax, so I searched to find an alternative way.

It’s suggested to use subqueries:

Let’s say we want to increase salaries of sales stuff:

This query will give the following error:

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

To be able to make this query work, we should ensure that Oracle will match each row of EMP to only one row at DEP (there shouldn’t be two departments with same department ID). So we create a primary key on ID column:

So the update query starts to work.

Now let’s try another update with same approach:

Oops, we got ORA-01779 again.

Unfortunately, this time we can not fix it by creating a primary key because, we can not force each department will have only one stuff. So we’ll use a different method:

It works! Now, let’s try to rewrite our first query:

It worked again even without creating a primary key.

Please share

AWS Big Data Specialist. Oracle Certified Professional (OCP) for EBS R12, Oracle 10g and 11g. Co-author of "Expert Oracle Enterprise Manager 12c" book published by Apress. Awarded as Oracle ACE (in 2011) and Oracle ACE Director (in 2016) for the continuous contributions to the Oracle users community. Founding member, and vice president of Turkish Oracle User Group (TROUG). Presented at various international conferences including Oracle Open World.

Leave Comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.