SQL UPDATE with INNER JOIN in Oracle

In Oracle, the UPDATE statement is used to modify existing records in a table. Oracle does not directly support UPDATE… Instead, you can achieve this by using alternative approaches like a correlated subquery, a MERGE statement, and the FROM clause.

In this blog, let’s explore the different approaches to updating the records with INNER JOIN on Oracle.

Table of Contents:

• Methods to Use UPDATE with INNER JOIN on Oracle
  • Method 1: Using a MERGE Statement in Oracle
  • Method 2: Using a Correlated Subquery in Oracle
• Alternative Approaches for UPDATE Statement with INNER JOIN in Oracle
  • Method 1: Using a Temporary Table in Oracle
  • Method 2: Using PL/SQL Cursor in Oracle
  • Method 3: Using EXISTS Subquery in Oracle
• Performance Comparison of Each Method
• Best Practices
• Real-world Use Case
• Conclusion

What is SQL UPDATE with INNER JOIN on Oracle?

The UPDATE Statement with INNER JOIN on Oracle is used to update records in one table based on the matching values from another table. Since Oracle does not support the UPDATE statement with JOIN directly, the MERGE statement can be used to achieve the same result.

Why do we need an Update with INNER JOIN on Oracle?

We need the UPDATE Statement with INNER JOIN on Oracle when we want to update records in one table based on related data in another table. This is useful in scenarios where the data is normalized across multiple tables. The common use cases include updating the prices or discounts and updating employee salaries based on department changes.

Advantages of UPDATE with INNER JOIN on Oracle

1. Ensures Data Consistency: It only updates matching records, preventing accidental updates to unrelated data 
2. Optimized Performance: When using MERGE, Oracle optimizes the execution plan, making it more efficient and reducing the need for a procedural loop
3. Simplifies Complex Updates: eliminates the need for multiple UPDATE statements by combining the logic in a single query.

Before getting into the methods, let us create two tables for performing the different approaches of INNER JOIN and consider these two tables as an example for the following methods.

Firstly, let’s create a target table called Employee and insert some of the values into it

-- Creating the target table (Employees)
CREATE TABLE employees (
    emp_id NUMBER PRIMARY KEY,
    emp_name VARCHAR2(50),
    salary NUMBER
);

-- Insert sample data into Employees
INSERT INTO employees VALUES (101, 'Alice', 5000);
INSERT INTO employees VALUES (102, 'Bob', 6000);
INSERT INTO employees VALUES (103, 'Charlie', 7000);

Now let’s create a Source table called Salary Updates and insert some values into it

-- Creating the source table (Salary Updates)
CREATE TABLE salary_updates (
    emp_id NUMBER PRIMARY KEY,
    new_salary NUMBER
);

-- Insert sample data into Salary Updates
INSERT INTO salary_updates VALUES (101, 5500);
INSERT INTO salary_updates VALUES (102, 6500);

Methods to Use UPDATE with INNER JOIN on Oracle

Method 1: Using a MERGE Statement in Oracle

The MERGE Statement in Oracle is a powerful DML operation that allows you to perform an UPDATE using an INNER JOIN between two tables. It is often referred to as an “Upsert” operation because it can update existing records and insert new records if needed.

Syntax:

MERGE INTO target_table t
USING (SELECT column1, column2, common_key FROM source_table) s
ON (t.common_key = s.common_key)
WHEN MATCHED THEN
    UPDATE SET t.column1 = s.column1,
               t.column2 = s.column2;

Example:

MERGE INTO employees e
USING salary_updates su
ON (e.emp_id = su.emp_id)
WHEN MATCHED THEN
    UPDATE SET e.salary = su.new_salary;

--To display the table
select * from employees;

Output:

Explanation: Here, the MERGE Statement specifies that the Employees table is the target table, which has to be updated by using the salary_updates as the source table.

Method 2: Using a Correlated Subquery in Oracle

The correlated subquery in Oracle updates the target table and fetches the record from the source table using the subquery. It is useful when updating a single column or a few columns based on a single matching condition.

Syntax:

UPDATE target_table t
SET column1 = (
    SELECT s.column1
    FROM source_table s
    WHERE s.common_key = t.common_key
)
WHERE EXISTS (
    SELECT 1 FROM source_table s WHERE s.common_key = t.common_key
);

Example:

UPDATE employees e
SET salary = (
    SELECT su.new_salary 
    FROM salary_updates su 
    WHERE su.emp_id = e.emp_id
)
WHERE EXISTS (
    SELECT 1 FROM salary_updates su WHERE su.emp_id = e.emp_id
);
-- To display the table
select * from employees;

Output:

Explanation: Here, the correlated subquery retrieves the new salary for each matching emp_id from the salary_updates and the WHERE Clause ensures that the update only happens for employees who have a corresponding salary update.

Alternative Approaches for UPDATE Statement with INNER JOIN in Oracle

Method 1: Using a Temporary Table in Oracle

A temporary table in Oracle is a special table that holds data only for the duration of a session or transaction. This Global Temporary Table allows us to store updated data temporarily before applying changes to the main table.

Syntax:

-- Create a Global Temporary Table (Data is session-specific)
CREATE GLOBAL TEMPORARY TABLE temp_updates (
    emp_id NUMBER PRIMARY KEY,
    new_salary NUMBER
) ON COMMIT PRESERVE ROWS;

-- Insert Data into Temporary Table
INSERT INTO temp_updates (emp_id, new_salary)
SELECT emp_id, new_salary FROM source_table;

-- Perform the Update Using the Temporary Table
UPDATE target_table t
SET t.salary = (SELECT tu.new_salary FROM temp_updates tu WHERE tu.emp_id = t.emp_id)
WHERE EXISTS (SELECT 1 FROM temp_updates tu WHERE tu.emp_id = t.emp_id);

-- Drop Temporary Table After Update (If Needed)
DROP TABLE temp_updates;

Example:

CREATE GLOBAL TEMPORARY TABLE temp_updates (
    emp_id NUMBER PRIMARY KEY,
    new_salary NUMBER
) ON COMMIT PRESERVE ROWS;
-- Insert only employees with even emp_id
INSERT INTO temp_updates (emp_id, new_salary)
SELECT emp_id, new_salary FROM salary_updates WHERE MOD(emp_id, 2) = 0;

-- Perform the update
UPDATE employees e
SET salary = (SELECT tu.new_salary FROM temp_updates tu WHERE tu.emp_id = e.emp_id)
WHERE EXISTS (SELECT 1 FROM temp_updates tu WHERE tu.emp_id = e.emp_id);

--To display the table
select * from employees;

Output:

Explanation: Here, the “MOD(emp_id, 2) = 0” ensures that only the employees with even emp_id values are added to the Temporary table. The employees table updates only matching records using WHERE EXISTS, leaving others unchanged.

Method 2: Using PL/SQL Cursor in Oracle

A PL/SQL cursor-based update is a method of updating records row by row using a cursor in Oracle PL/SQL.

Syntax:

DECLARE
    CURSOR cursor_name IS
        SELECT column1, column2, ... FROM table_name WHERE condition;
BEGIN
    FOR record_variable IN cursor_name LOOP
        UPDATE table_name
        SET column1 = new_value, column2 = new_value
        WHERE primary_key_column = record_variable.primary_key_column;
    END LOOP;
    COMMIT;
END;
/

Example:

DECLARE
    CURSOR emp_cursor IS
        SELECT emp_id, salary * 1.10 AS new_salary FROM employees;
BEGIN
    FOR emp_rec IN emp_cursor LOOP
        UPDATE employees 
        SET salary = emp_rec.new_salary
        WHERE emp_id = emp_rec.emp_id;
    END LOOP;
    COMMIT;
END;
/

-- To display the table
SELECT emp_id, emp_name, salary 
FROM employees;

Output:

Explanation: Here, the SELECT Statement selects each emp_id, and the CURSOR in this query is used to iterate over employee records and calculate a new salary with a 10% increase. The COMMIT saves all changes after all the records are processed.

Method 3: Using EXISTS Subquery in Oracle

This EXISTS subquery updates records in one table based on the presence of other records in another specific table.

Syntax:

UPDATE target_table t
SET t.column_name = new_value
WHERE EXISTS (
    SELECT 1 FROM source_table s
    WHERE s.join_column = t.join_column
);

Example:

CREATE INDEX idx_emp_id ON employees(emp_id);
CREATE INDEX idx_salary_updates ON salary_updates(emp_id);
UPDATE employees e
SET e.salary = (SELECT s.new_salary FROM salary_updates s WHERE s.emp_id = e.emp_id)
WHERE EXISTS (
    SELECT 1 FROM salary_updates s WHERE s.emp_id = e.emp_id
);

--To display the table
SELECT * FROM employees;

Output:

Explanation: Here, the EXISTS condition restricts the UPDATE to those who have corresponding rows in this table (salary_updates). The subquery finds out the value of new_salary accordingly. Thus Alice’s and Bob’s salaries are updated too.

Performance Comparison of Each Method

Best Practices

• Use MERGE for large updates: MERGE is the best one for bulk updates because Oracle optimizes it and avoids updates on non-matches.
• Use EXISTS in a subquery, which works faster: Instead of IN, try the EXISTS keyword.
•  Put indexes on join columns: Improves performance because lookups are much more efficient.
• Never update primary keys: Changing primary key values would ruin data integrity.
• Commit wisely: Do not commit repeatedly in loops, rather commit after batch updates.

Real-world Use Case

1. HR System: Considering updates to the employee salary according to the recent appraisal records in another table by HR.

Example:

-- Employee Table (Current Salaries)
CREATE TABLE employees (
    emp_id NUMBER PRIMARY KEY,
    emp_name VARCHAR2(50),
    salary NUMBER
);

-- Appraisal Table (New Salaries)
CREATE TABLE salary_appraisals (
    emp_id NUMBER PRIMARY KEY,
    new_salary NUMBER
);

-- Insert Sample Data
INSERT INTO employees VALUES (101, 'Mark', 5000);
INSERT INTO employees VALUES (102, 'John', 6000);
INSERT INTO employees VALUES (103, 'Frank', 7000);
COMMIT;
INSERT INTO salary_appraisals VALUES (101, 5500);
INSERT INTO salary_appraisals VALUES (102, 6500);
COMMIT;
UPDATE employees e
SET e.salary = (SELECT s.new_salary FROM salary_appraisals s WHERE s.emp_id = e.emp_id)
WHERE EXISTS (SELECT 1 FROM salary_appraisals s WHERE s.emp_id = e.emp_id);

--To display the table
SELECT * FROM employees;

Output:

Explanation: Mark and John’s salary is updated according to their appraisal records, so Frank’s salary is the same since he does not have a record in salary_appraisal.

2. Retail Store: A retail store requires that the price of products be modified based on subsidies provided by suppliers, so it needs to update the product price.

Example:

-- Product Table (Current Prices)
CREATE TABLE products (
    product_id NUMBER PRIMARY KEY,
    product_name VARCHAR2(50),
    price NUMBER
);

-- Discounts Table (New Prices)
CREATE TABLE supplier_discounts (
    product_id NUMBER PRIMARY KEY,
    discounted_price NUMBER
);

-- Insert Sample Data
INSERT INTO products VALUES (201, 'Laptop', 1000);
INSERT INTO products VALUES (202, 'Phone', 800);
INSERT INTO products VALUES (203, 'Tablet', 600);
COMMIT;
INSERT INTO supplier_discounts VALUES (201, 900);
INSERT INTO supplier_discounts VALUES (202, 750);
COMMIT;

--Update Product prices 
UPDATE products p
SET p.price = (SELECT d.discounted_price FROM supplier_discounts d WHERE d.product_id = p.product_id)
WHERE EXISTS (SELECT 1 FROM supplier_discounts d WHERE d.product_id = p.product_id);

--To display the table
SELECT * FROM products;

Output:

Explanation: Laptop and phone prices are updated based on the supplier discounts. The tablet’s price remains the same since no discount is there for it.

3. Grade System: A university wants to update student’s grades based on the latest exam scores stored in another table.

Example:

-- Students Table (Current Grades)
CREATE TABLE students (
    student_id NUMBER PRIMARY KEY,
    student_name VARCHAR2(50),
    grade VARCHAR2(2)
);

-- Exam Results Table (New Grades)
CREATE TABLE exam_results (
    student_id NUMBER PRIMARY KEY,
    new_grade VARCHAR2(2)
);

-- Insert Sample Data
INSERT INTO students VALUES (301, 'John', 'B');
INSERT INTO students VALUES (302, 'Emily', 'C');
INSERT INTO students VALUES (303, 'Mark', 'A');
COMMIT;
INSERT INTO exam_results VALUES (301, 'A');
INSERT INTO exam_results VALUES (302, 'B');
COMMIT;
UPDATE students s
SET s.grade = (SELECT e.new_grade FROM exam_results e WHERE e.student_id = s.student_id)
WHERE EXISTS (SELECT 1 FROM exam_results e WHERE e.student_id = s.student_id);

--To display the table 
SELECT * FROM students;

Output:

Explanation: John and Emily’s grades are updated based on the new exam results. Mark’s grade is unchanged since no new results are recorded for him.

Conclusion 

Oracle efficiently updates records using INNER JOIN to provide data integrity among the related tables. Various methods, such as MERGE and correlated subquery, introduce some degree of flexibility, each suited for different use cases. The choice you make will play a crucial role in optimizing performance. You might have gotten the idea here in this blog of various techniques for updating statements with INNER JOIN on Oracle. To learn more about SQL, check out the SQL Certification 

FAQs