Updating Rows Based on Subquery Result
In this approach, we’ll utilize a subquery within the UPDATE statement to update table rows based on specific conditions.
Syntax:
UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition IN (subquery);
Example: Increasing Salaries for Sales Department Employees
Suppose we want to increase the salaries of employees in the Sales department by 10%. So, in this example we will use subquery in the condition of query. In the subquery, we selected deptid from departments belonging to Sales department.
UPDATE employees
SET salary = salary * 1.1
WHERE deptid = (SELECT deptid FROM departments WHERE deptname = 'Sales');
Output:
Explanation: In this example, the query updates the salary column for employees in the Sales department. It multiplies their current salaries by 1.1, effectively increasing them by 10%.
How to Update Table Rows in PL/SQL Using Subquery?
Updating table rows in PL/SQL via subqueries allows precise data modifications based on specific conditions. By integrating subqueries within the UPDATE statement, rows can be selectively targeted for updates, enhancing data management efficiency.
This article explores the concept of updating rows in PL/SQL using subqueries, providing insights into syntax, examples, and practical applications, contributing to better data handling and application development.