PL/SQL UPDATE Statement

The UPDATE statement in the PL/SQL(Procedural Language/ Structural Query Language) is the powerful SQL (StructuredQueryLanguage) command used to modify the existing data in the database table.

In this article, we will explain the PL/SQL UPDATE Statement, its syntax, and examples in detail.

PL/SQL UPDATE Statement

The UPDATE statement in the PL/SQL is allowed to alter one or more columns in one or more rows of the table. The UPDATE statement is specifically designed to change existing data.

The scope of the update statement is controlled with the help of the WHERE clause. Which filter rows that need to be updated based on certain conditions?

Syntax:

UPDATE table_name
SET column1 = value1,
 column2 = value2,
 ...
WHERE condition;

key terms:

• table_name is the name of the table where we want to update the data of the table.
  
• SET column1 = value1, column2 = value2, ... SET clause specifies the columns we want to update and the new values assigned to those columns. we can update one or more columns in the single UPDATE statement.
  
• WHERE condition is the WHERE clause, it is optional but highly recommended. If we are not using the WHERE clause, all rows in the table are updated.

Employees Table:

• In the below code, CREATETABLE statement is used to create the table with respective columns.
• INSERTINTO statement is used to insert the values into the employees table with the values of employee_id, first_name, last_name and salary.

Query:

CREATE TABLE employees (
 employee_id NUMBER PRIMARY KEY,
 first_name VARCHAR2(20),
 last_name VARCHAR2(20),
 salary NUMBER(10, 2)
);


INSERT INTO employees (employee_id, first_name, last_name, salary) 
VALUES (1001, 'John', 'Doe', 55000);

INSERT INTO employees (employee_id, first_name, last_name, salary) 
VALUES (1002, 'Jane', 'Smith', 60000);

INSERT INTO employees (employee_id, first_name, last_name, salary) 
VALUES (1003, 'Mary', 'Johnson', 70000);

INSERT INTO employees (employee_id, first_name, last_name, salary) 
VALUES (1004, 'Alice', 'Brown', 55000);

INSERT INTO employees (employee_id, first_name, last_name, salary) 
VALUES (1005, 'Bob', 'Davis', 62000);

Output:

Salary_adjustments Table

• In the below code, CREATETABLE statement is used to create the table with respective columns.
• INSERTINTO statement is used to insert the values into the salary_adjustments table with the values of employee_id, and new_salary.

Query:

CREATE TABLE salary_adjustments (
 employee_id NUMBER PRIMARY KEY,
 new_salary NUMBER(10, 2)
);

-- Insert some sample salary adjustments
INSERT INTO salary_adjustments (employee_id, new_salary) VALUES (1001, 58000);
INSERT INTO salary_adjustments (employee_id, new_salary) VALUES (1003, 82000);
INSERT INTO salary_adjustments (employee_id, new_salary) VALUES (1005, 64000);

Output:

Example 1: Update Single Column

In this query, the UPDATE statement increases the salary of the employee with employee_id = 1002 by 10% . The changes are made permanent by the COMMIT statement, and the message 'Salary updated for employee 1002' is printed using DBMS_OUTPUT.PUT_LINE.

Query:

BEGIN
 UPDATE employees
 SET salary = salary * 1.1
 WHERE employee_id = 1002;

 COMMIT; -- Commit the changes to make them permanent

 DBMS_OUTPUT.PUT_LINE('Salary updated for employee 1002.');
END;
/

Output:

Explanation:

After executing the query, the salary for employee Jane Smith (employee_id 1002) is updated from 60000 to 66000 (10% increase). The rest of the employees' data remains unchanged.

Example 2: Update Multiple Columns

This query updates two columns for the employee with employee_id = 1003. The last_name is changed from 'Johnson' to 'Williams', and the salary is increased by 15%.

The changes are finalized with the COMMIT statement, and a message confirming the update is printed using DBMS_OUTPUT.PUT_LINE.

Query:

BEGIN
 UPDATE employees
 SET last_name = 'Williams',
 salary = salary * 1.15
 WHERE employee_id = 1003;

 COMMIT; -- Commit the changes to make them permanent

 DBMS_OUTPUT.PUT_LINE('Last name and salary updated for employee 1003.');
END;
/

Output:

Explanation:

After running the query, employee Mary Johnson's last name is updated to 'Williams', and her salary is raised from 70000 to 80500. The other employees' details remain unchanged.

Example 3: Update Table with Data from Another Table

This query updates the salary of employees based on data from the salary_adjustments table. The subquery selects the new_salary from the salary_adjustments table where the employee_id matches.

The EXISTS clause ensures that only those employees present in the salary_adjustments table have their salaries updated. The changes are committed with the COMMIT statement, making the updates permanent.

Query:

BEGIN
 UPDATE employees e
 SET e.salary = (
 SELECT sa.new_salary
 FROM salary_adjustments sa
 WHERE sa.employee_id = e.employee_id
 )
 WHERE EXISTS (
 SELECT 1
 FROM salary_adjustments sa
 WHERE sa.employee_id = e.employee_id
 );

 COMMIT; -- Commit the changes to make them permanent

 DBMS_OUTPUT.PUT_LINE('Employee salaries updated from salary_adjustments table.');
END;
/

Output:

Explanation:

• In the output, employees John Doe (1001), Mary Williams (1003), and Bob Davis (1005) have their salaries updated to the values from the salary_adjustments table: 58000, 82000, and 64000, respectively.
  
• Other employees like Jane Smith (1002) and Alice Brown (1004) remain unaffected, as they are not present in the salary_adjustments table.

Example 4: Using EXISTS Clause

This query updates the salaries of employees listed in the salary_adjustments table, increasing them by 5%. The EXISTS clause is used to check if an employee in the employees table has a corresponding entry in the salary_adjustments table.

If an employee exists in both tables, their salary is multiplied by 1.05 to give them a 5% increase. The COMMIT command finalizes the changes.

Query:

BEGIN
 UPDATE employees e
 SET e.salary = e.salary * 1.05
 WHERE EXISTS (
 SELECT 1
 FROM salary_adjustments sa
 WHERE sa.employee_id = e.employee_id
 );

 COMMIT; -- Commit the changes to make them permanent

 DBMS_OUTPUT.PUT_LINE('Salaries increased by 5% for employees in the salary_adjustments table.');
END;
/

Output:

Explanation:

• In the output, employees John Doe (1001), Mary Williams (1003), and Bob Davis (1005), who are present in the salary_adjustments table, have their salaries increased by 5%.
  
• For example, John's salary increases from 58000 to 60900.
  
• Employees Jane Smith (1002) and Alice Brown (1004) are unaffected as they are not part of the salary_adjustments table.

Conclusion

The PL/SQL UPDATE statement is a powerful tool for modifying existing data in a database. It allows developers to update one or more columns or across all rows.

The WHERE clause can be used to control the affected rows, and the COMMIT statement ensures that the changes are permanent. By combining the UPDATE statement with transaction control and error handling, developers can ensure data integrity and prevent concurrency issues.