Convert MySQL update SQL statement to Oracle update statement

Question

I have query which is working fine with MySQL, but if I execute it in Oracle database I get the following error:

SQL Error: ORA-01779: cannot modify a column which maps to a non key-preserved table
01779. 00000 -  "cannot modify a column which maps to a non key-preserved table"
*Cause:    An attempt was made to insert or update columns of a join view which
           map to a non-key-preserved table.
*Action:   Modify the underlying base tables directly

MySQL query:

UPDATE  T1 
  INNER JOIN  T2 ON  T1.UIDPK =T2.UIDFK
  SET T1.C1=CONCAT('EMPTY_',T2.UID) WHERE T2.C1 IS NULL ;

Changed query for Oracle:

 UPDATE 
(
 SELECT T1.C1 AS OLD ,CONCAT('EMPTY_',T2.UID) AS NEW FROM  T1 
 INNER JOIN  T2 ON T1.UIDPK= T2.UIDFK WHERE T1.C1 IS NULL
) T3
SET T3.OLD = T3.NEW

Above query is not working in Oracle database.

score 0 · Answer 1 · answered Oct 23 '19 at 14:23

The update syntax you are using only works if your version of Oracle decides that the subquery aliased as T3 is an updatable view. You may use a correlated subquery instead of this:

UPDATE T1
SET C1 = (SELECT CONCAT('EMPTY_', T2.UID) FROM T2
          WHERE T1.UIDPK = T2.UIDFK AND T2.C1 IS NULL);

score 0 · Answer 2 · answered Oct 29 '19 at 09:42

You can also check if you have indexes on T2.UIDFK and T1.UIDP columns.

If not, create them and your update might work after that.

Not having indexes on those columns would result in this error / restriction.

Note:

You can always delete those indexes after this update, although it would seem that those are intended as a foreign key and a primary key column, respectively, and it's always good to have created FK and PK constraints on such columns (which would also lead to existing indexes on those columns).

Convert MySQL update SQL statement to Oracle update statement

2 Answers2