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.
Setting up Environment
First, let’s create a table named employees with columns employee_id, salary, and department_id.
Query:
-- Create table employees
CREATE TABLE employees1 (
employee_id INT,
salary FLOAT,
department_id INT
);
-- Create table departments
CREATE TABLE departments (
DEPTID INT PRIMARY KEY,
DEPTNAME VARCHAR(50)
);
Inserting Values into the employees Table. Now, let’s insert some sample data into the employees table.
--Inserting values into employees table.
INSERT INTO employees1 VALUES (1, 50000, 1);
INSERT INTO employees1 VALUES (2, 60000, 1);
INSERT INTO employees1 VALUES (3, 45000, 2);
INSERT INTO employees1 VALUES (4, 55000, 3);
--Inserting values into departments table.
INSERT INTO departments (DEPTID, DEPTNAME) VALUES (1, 'Sales');
INSERT INTO departments (DEPTID, DEPTNAME) VALUES (2, 'Finance');
INSERT INTO departments (DEPTID, DEPTNAME) VALUES (3, 'HR');
INSERT INTO departments (DEPTID, DEPTNAME) VALUES (4, 'Marketing');
SELECT * FROM employees;
SELECT * FROM departments;
Employees Table:
Employees Table
Departments Table:
Departments Table
How to Update Table Rows in PL/SQL Using Subquery
Where we use more than one select statement, it is called Subquery. Updating table rows in PL/SQL using subqueries involves using a subquery within the UPDATE statement to specify which rows to update and how they should be updated. The subquery acts as a filter, allowing you to selectively update rows based on certain conditions. It is also known as inner querry.
- Updating Rows Based on Subquery Result
- Updating Rows Based on Subquery Result
- Updating Rows Based on Subquery Result (Using Aggregate Function)
Note: We need to write N before the subquery if we want to add multiple conditions in subquery.
1. 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:
Updating Rows Based on a Subquery Result
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%.
2. Updating Rows Based on Subquery Result
In this scenario, we’ll demonstrate updating table rows based on multiple conditions specified in a subquery.
Syntax:
UPDATE table_name
SET column = value
WHERE condition1 IN (subquery) AND condition2 < value;
Example: Providing Bonus to Finance Department Employees
Suppose we want to give a bonus of 1000 to employees in the Finance department whose salary is less than 60000.
Query:
UPDATE employees
SET salary = salary + 1000
WHERE deptid = (SELECT deptid FROM departments WHERE deptname = 'Finance')
AND salary < 60000;
Output:
Updating Rows Based on a Subquery Result
Explanation: This query adds 1000 to the salary column for employees in the Finance department whose salary is less than 60000, effectively giving them a bonus.
3. Updating Rows Based on Subquery Result (Using Aggregate Function)
Here, we’ll showcase updating table rows based on aggregated subquery results, providing a more complex yet powerful data modification technique.
Syntax:
UPDATE table_name
SET column = value
WHERE condition IN (SELECT column FROM table GROUP BY column HAVING aggregate_function < value);
Example: Salary Adjustment for Departments with Average Salary Below 55000
Suppose we want to increase the salaries of employees in departments where the average salary is less than 55000 by 5%.
Query:
UPDATE employees
SET salary = salary * 1.05
WHERE deptid IN (SELECT deptid FROM employees GROUP BY deptid HAVING AVG(salary) < 55000);
Output:
Updating Rows Based on a Subquery Result (Using Aggregate Function)
Explanation: This query updates the salary column for employees in departments with an average salary less than 55000. It multiplies their current salaries by 1.05, effectively increasing them by 5%.
Conclusion
Updating table rows in PL/SQL using subqueries is a flexible way to modify data based on specific conditions. By utilizing subqueries within the UPDATE statement, you can efficiently update rows in accordance with various requirements, ultimately contributing to better data management and application development.