Sayantan's Blog On Oracle

Sayantan's blogs on Oracle

Learn Oracle with Sayantan

Update multiple rows in Oracle using Join

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;
Update multiple rows in Oracle using Join: Output
Update multiple rows in Oracle using Join: Output

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;
Update multiple rows in Oracle using Join: Output

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;
Update multiple rows in Oracle using Join: Output

Using MERGE statement results the same output.

RELATED TOPICS:

Leave a Comment

Your email address will not be published.