UPDATE Statements

The UPDATE statement is used to modify existing data within a table. You can update all rows, specific rows based on conditions, or leverage subqueries for more complex updates. It is part of data manipulation language (DML).

Updating All Rows

This type of UPDATE statement modifies a specific column or columns for every single row in the table. Use with caution, as it affects the entire dataset.

Example 1: Oracle 19c SQL UPDATE - All Rows

UPDATE employees
SET commission_pct = 0.10;
-- Sets the commission percentage to 10% for all employees.

Explanation This statement updates the commission_pct column to 0.10 for every row in the employees table. This is a global update affecting all records.

Updating Specific Rows with WHERE Clause

This is the most common use of UPDATE, allowing you to modify data for rows that meet a specified condition. The WHERE clause is crucial for targeted data changes.

Example 2: Oracle 19c SQL UPDATE - Specific Rows

UPDATE employees
SET salary = salary * 1.05
WHERE job_id = 'IT_PROG';
-- Increases the salary of all employees with the job ID 'IT_PROG' by 5%.

Explanation This code updates the salary for only those employees whose job_id is IT_PROG. The WHERE clause ensures that only a specific subset of rows is affected.

Updating with Subqueries

Subqueries within an UPDATE statement allow you to set column values based on results from another query. This enables complex data derivations and cross-table updates.

Example 3: Oracle 19c SQL UPDATE - With Subquery

UPDATE employees e
SET e.salary = (SELECT d.max_salary
               FROM jobs d
               WHERE e.job_id = d.job_id)
WHERE e.salary < (SELECT d.max_salary
                  FROM jobs d
                  WHERE e.job_id = d.job_id);
-- Updates an employee's salary to their job's maximum salary if their current salary is below it.

Explanation This UPDATE statement uses a subquery to determine the max_salary for each employee's job_id. If an employee's current salary is less than their job's maximum, their salary is updated to that maximum.