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:
|
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'; |