Sayantan's Blog On Oracle

Update multiple rows in Oracle using Join

We will check multiple ways to update rows using join condition.

Creating setup for this testing:

--Update multiple rows in Oracle using Join: Setup

CREATE TABLE EMP_JOIN_TEST
AS
SELECT EMPLOYEE_ID,
       FIRST_NAME,
       LAST_NAME,
       JOB_ID,
       HIRE_DATE,
       SALARY,
       DEPARTMENT_ID
FROM EMPLOYEES
WHERE DEPARTMENT_ID IN(10, 20, 30);

SELECT * FROM EMP_JOIN_TEST;

Adding a new column DEPARTMENT_NAME to test the update cases.

ALTER TABLE EMP_JOIN_TEST ADD(DEPARTMENT_NAME VARCHAR2(30));

Query as a data source:

In this scenario we will use the join between 2 tables as a data source. Then update one column with the same column value of other.

UPDATE (SELECT A.DEPARTMENT_ID,
               A.DEPARTMENT_NAME,
               B.DEPARTMENT_NAME DEPTNAME_MST
         FROM EMP_JOIN_TEST A,
              DEPARTMENTS B
         WHERE A.DEPARTMENT_ID = B.DEPARTMENT_ID
         ) T
SET T.DEPARTMENT_NAME = T.DEPTNAME_MST;

10 rows updated.

SELECT * FROM EMP_JOIN_TEST;

In the above example we have used the joining between EMP_JOIN_TEST and DEPARTMENTS table and update the DEPARTMENT_NAME column of EMP_JOIN_TEST

ROLLBACK;

Value as a result of a query:

In this scenario, we will use a value which is the result of a join condition to update multiple rows of a column.

UPDATE EMP_JOIN_TEST A
SET DEPARTMENT_NAME = (SELECT DEPARTMENT_NAME
                       FROM DEPARTMENTS
                       WHERE DEPARTMENT_ID = A.DEPARTMENT_ID
                       )
WHERE DEPARTMENT_ID IS NOT NULL;

10 rows updated.

SELECT * FROM EMP_JOIN_TEST;

In the above example we have used the join condition to update the DEPARTMENT_NAME column of EMP_JOIN_TEST by the same column of DEPARTMENTS table.

ROLLBACK;

Merge Statement:

MERGE INTO EMP_JOIN_TEST C
USING DEPARTMENTS T
ON (C.DEPARTMENT_ID = T.DEPARTMENT_ID)
WHEN MATCHED THEN UPDATE 
SET C.DEPARTMENT_NAME = T.DEPARTMENT_NAME;

10 rows merged.

SELECT * FROM EMP_JOIN_TEST;

Using MERGE statement results the same output.

RELATED TOPICS:

• How to fetch multiple columns from cursor in Oracle?