Updating Data

The UPDATE statement modifies existing rows in a table. It can change a single column in a single row, or update dozens of columns across millions of rows in one statement. Without a WHERE clause every row in the table is updated — a mistake that is easy to make and expensive to recover from. This lesson covers single and multi-column updates, subquery-based updates that derive values from other tables, conditional updates using CASE, and how Oracle's transaction model protects you until you commit.

To follow along with the examples in this lesson, use the course dataset from Lesson 8.

Single and Multi-Column UPDATE

The basic UPDATE syntax names the table, lists column assignments in a SET clause, and narrows the affected rows with WHERE. Multiple columns are assigned in a single SET clause separated by commas — there is no need for a second SET keyword. All assignments in the same SET clause are based on the row's values before the update begins, so the order of assignments does not matter.

-- Single column update — correct the loyalty tier for one customer
UPDATE customers
SET    loyalty_tier = 'gold'
WHERE  customer_id = 4;

-- Verify the change
SELECT customer_id, full_name, loyalty_tier
FROM   customers
WHERE  customer_id = 4;

-- Multi-column update — update price and stock for a product in one statement
UPDATE products
SET    unit_price = 134.99,
       stock_qty  = stock_qty + 50
WHERE  product_id = 3;

-- Verify
SELECT product_name, unit_price, stock_qty
FROM   products
WHERE  product_id = 3;

-- Update using an expression — apply a 10% price increase to all Fitness products
UPDATE products
SET    unit_price = ROUND(unit_price * 1.10, 2)
WHERE  category = 'Fitness';

-- Update a DATE column
UPDATE employees
SET    hire_date = DATE '2023-06-15'
WHERE  employee_id = 105;

• All column assignments in the same SET clause read the original row values — SET a = b, b = a correctly swaps the two values because both read the pre-update state
• Always verify the row count in the feedback message — 1 row updated confirms the WHERE clause matched exactly one row; 0 rows updated means the filter matched nothing and is often a sign of a condition error
• An UPDATE without a WHERE clause updates every row in the table — Oracle does not warn you; double-check the WHERE clause before executing any update in a production environment

Subquery-Based UPDATE

When the new value for a column must be derived from another table, a subquery in the SET clause fetches it. The subquery must return exactly one row and one column for each column being updated — returning more than one row raises ORA-01427. If the subquery returns no rows the column is set to NULL, so always verify the subquery returns a result before running the update.

A correlated subquery in the WHERE clause can also restrict which rows are updated based on related data in another table — without needing a join in the UPDATE itself.

-- Update total_amount on each order to reflect the sum of its order items
-- The subquery is correlated — it references the outer order's order_id
UPDATE orders o
SET    total_amount = (
           SELECT SUM(quantity * unit_price)
           FROM   order_items oi
           WHERE  oi.order_id = o.order_id
       );

-- Verify a sample
SELECT order_id, total_amount
FROM   orders
ORDER BY order_id
FETCH FIRST 5 ROWS ONLY;

-- Update employee salaries to match the average for their department
UPDATE salaries s
SET    amount = (
           SELECT ROUND(AVG(s2.amount), 2)
           FROM   salaries  s2
           JOIN   employees e2 ON e2.employee_id = s2.employee_id
           WHERE  e2.department_id = (
                      SELECT department_id
                      FROM   employees e3
                      WHERE  e3.employee_id = s.employee_id
                  )
       )
WHERE  EXISTS (
           SELECT 1
           FROM   employees e
           WHERE  e.employee_id = s.employee_id
           AND    e.department_id = 20
       );

• If the subquery in SET returns no rows the column is set to NULL — for NOT NULL columns this raises ORA-01407; always add a WHERE EXISTS clause or a NVL guard if the subquery might return nothing
• The correlated subquery re-executes once for every row that matches the UPDATE's WHERE clause — on large tables this can be slow; consider using a MERGE statement for better performance on bulk updates
• Test subquery-based updates by running the subquery as a standalone SELECT first — confirm it returns the expected single value for a sample of rows before attaching it to the UPDATE

Conditional UPDATE with CASE

A CASE expression inside the SET clause applies different values to different rows in a single UPDATE statement. Without CASE you would need separate UPDATE statements for each condition — one statement with CASE is faster because it scans the table once and applies all the logic in a single pass.

-- Reclassify loyalty tiers based on total spend in a single UPDATE
UPDATE customers c
SET    loyalty_tier = (
           CASE
               WHEN (SELECT NVL(SUM(total_amount), 0)
                     FROM   orders o
                     WHERE  o.customer_id = c.customer_id) > 1000 THEN 'platinum'
               WHEN (SELECT NVL(SUM(total_amount), 0)
                     FROM   orders o
                     WHERE  o.customer_id = c.customer_id) > 500  THEN 'gold'
               ELSE 'standard'
           END
       );

-- CASE update across multiple columns — adjust price and active flag by stock level
UPDATE products
SET    active_flag = CASE
                         WHEN stock_qty = 0  THEN 'N'
                         ELSE                     'Y'
                     END,
       unit_price  = CASE
                         WHEN stock_qty = 0  THEN ROUND(unit_price * 0.80, 2)
                         WHEN stock_qty < 20 THEN ROUND(unit_price * 0.90, 2)
                         ELSE                     unit_price
                     END;

-- Verify the tier reclassification
SELECT customer_id, full_name, loyalty_tier
FROM   customers
ORDER BY loyalty_tier, customer_id;

• A single UPDATE with CASE is more efficient than multiple targeted UPDATE statements because the table is scanned once — each additional UPDATE statement causes another full or partial scan
• When not all rows are covered by a CASE branch, always include an ELSE clause — without it, unmatched rows are set to NULL, which silently corrupts data
• Multiple columns can each have their own independent CASE expressions in the same SET clause — each column is evaluated separately across all rows in the same single pass

UPDATE and Oracle Transactions

In Oracle, every UPDATE is part of a transaction. Changes are not visible to other sessions until COMMIT is issued, and they can be fully reversed with ROLLBACK at any point before the commit. This is fundamentally different from databases that auto-commit each statement — in Oracle you are always in an explicit transaction.

SAVEPOINT lets you mark a point within a transaction and roll back to it without undoing earlier changes. This is useful in scripts that perform a sequence of updates where you want to reverse only the last step if something goes wrong.

-- First update in the transaction
UPDATE products
SET    unit_price = 599.99
WHERE  product_id = 1;

-- Mark a savepoint after the first update
SAVEPOINT after_price_update;

-- Second update
UPDATE products
SET    stock_qty = 0
WHERE  product_id = 1;

-- Something went wrong — roll back to the savepoint
-- The price update is preserved; the stock update is reversed
ROLLBACK TO SAVEPOINT after_price_update;

-- Verify: price changed, stock unchanged
SELECT product_id, unit_price, stock_qty
FROM   products
WHERE  product_id = 1;

-- Commit the price change permanently
COMMIT;

• Oracle does not auto-commit DML — INSERT, UPDATE, and DELETE are all pending until COMMIT is explicitly issued; closing a connection without committing causes an automatic ROLLBACK
• Other sessions cannot see your uncommitted changes — Oracle's read consistency model means they see the last committed state of the data, not your in-progress transaction
• SAVEPOINT names are session-scoped and reusable — creating a savepoint with the same name as an existing one replaces it; the older savepoint is lost

Preventing Accidental Full-Table Updates

The most dangerous UPDATE mistake is omitting the WHERE clause. Oracle provides no automatic safeguard — it will update every row without hesitation. Several safe practices reduce this risk in day-to-day work.

-- Safe practice 1: run the equivalent SELECT first to preview affected rows
SELECT product_id, product_name, unit_price
FROM   products
WHERE  category = 'Electronics'
AND    unit_price > 100;

-- If the SELECT returns the expected rows, run the UPDATE with confidence
UPDATE products
SET    unit_price = ROUND(unit_price * 0.95, 2)
WHERE  category = 'Electronics'
AND    unit_price > 100;

-- Safe practice 2: check SQL%ROWCOUNT in PL/SQL before committing
DECLARE
    v_rows  NUMBER;
BEGIN
    UPDATE customers
    SET    loyalty_tier = 'standard'
    WHERE  country = 'USA';

    v_rows := SQL%ROWCOUNT;

    IF v_rows > 5 THEN
        ROLLBACK;
        DBMS_OUTPUT.PUT_LINE('Unexpected row count: ' || v_rows || ' — rolled back.');
    ELSE
        COMMIT;
        DBMS_OUTPUT.PUT_LINE('Updated ' || v_rows || ' rows — committed.');
    END IF;
END;
/

• SQL%ROWCOUNT in PL/SQL returns the number of rows affected by the most recent DML statement — use it as a guard to catch unexpectedly large updates before committing
• Running the equivalent SELECT before an UPDATE is the single most effective habit for avoiding accidental full-table updates — it costs nothing and makes the affected rows visible before any change is made
• In SQL Developer, changes are not permanent until you click Commit — executing the statement and committing are separate actions, giving you a window to review and roll back if needed

Summary

Practice Questions

Quiz

Next up — Deleting Data — Learn how to remove rows safely using DELETE with WHERE filters, TRUNCATE for fast full-table clears, and how foreign key constraints affect deletion order.