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:
|
1 2 3 4 5 6 7 8 9 10 |
CREATE TABLE emp ( ID NUMBER, NAME VARCHAR2(100), sal NUMBER, dep_id NUMBER ); CREATE TABLE dep ( ID NUMBER, NAME VARCHAR2(50 ) ); INSERT INTO emp VALUES (1, 'GOKHAN', 10000, 1); INSERT INTO emp VALUES (2, 'OSMAN', 10000, 1); INSERT INTO emp VALUES (3, 'HAKAN', 10000, 1); INSERT INTO emp VALUES (4, 'ACAR', 5000, 2); INSERT INTO emp VALUES (5, 'CEMIL', 5000, 2); INSERT INTO dep VALUES (1, 'IT' ); INSERT INTO dep VALUES (2, 'SALES' ); COMMIT; |
Let’s say we want to increase salaries of sales stuff:
|
1 2 3 4 |
UPDATE (SELECT emp.sal, dep.name FROM emp JOIN dep ON emp.dep_id = dep.ID ) SET sal = sal * 1.2 WHERE name = 'SALES'; |
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:
|
1 |
ALTER TABLE dep ADD PRIMARY KEY (ID); |
So the update query starts to work.
Now let’s try another update with same approach:
|
1 2 3 4 |
UPDATE (SELECT dep.name dep, emp.name FROM emp JOIN dep ON emp.dep_id = dep.ID ) SET dep = 'MARKETING' WHERE name = 'GOKHAN'; |
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:
|
1 2 3 4 5 |
MERGE INTO dep USING emp ON (emp.dep_id = dep.ID AND emp.NAME = 'GOKHAN') WHEN MATCHED THEN UPDATE SET dep.name = 'MARKETING'; |
It works! Now, let’s try to rewrite our first query:
|
1 2 3 4 5 |
MERGE INTO emp USING dep ON (emp.dep_id = dep.ID AND dep.NAME = 'SALES') WHEN MATCHED THEN UPDATE SET emp.sal = emp.sal * 1.2; |
It worked again even without creating a primary key.
