Database Management SystemsSQL Fundamentals

Data Manipulation Language (DML)

LevelBeginner

Duration60 mins

TopicSQL Fundamentals

2 / 5

UPDATE Statement

Modifying Data in Place

While INSERT brings data into existence and DELETE removes it, UPDATE performs the delicate operation of modifying existing data. This operation carries significant responsibility—unlike INSERT, which adds rows that can be deleted, UPDATE changes data that may have been trusted for days, months, or years.

Mastering UPDATE means understanding not just how to change data, but how to change exactly the right data. An UPDATE without proper conditions can overwrite thousands of records in milliseconds, potentially causing catastrophic data loss. This page teaches you to wield UPDATE with precision and confidence.

What You Will Learn

By the end of this page, you will master UPDATE syntax for single and multiple columns, understand how WHERE clauses control update scope, perform conditional updates with CASE expressions, update data from other tables using joins and subqueries, and apply essential safety practices to prevent accidental mass updates.

UPDATE Fundamentals

The UPDATE statement modifies existing rows in a table. Its power lies in the combination of the SET clause (what to change) and the WHERE clause (which rows to change).

The Basic UPDATE Syntax:

update_basic_syntax.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
-- Complete UPDATE syntax pattern
UPDATE table_name
SET column1 = value1,
    column2 = value2,
    column3 = value3
WHERE condition;
 
-- The UPDATE statement has three key components:
-- 1. Target table: The table containing rows to modify
-- 2. SET clause: Column assignments specifying new values
-- 3. WHERE clause: Predicate identifying which rows to update (CRITICAL!)
 
-- Example: Update a single employee's salary
UPDATE employees
SET salary = 85000.00
WHERE employee_id = 1001;
 
-- This updates ONLY the row where employee_id = 1001
-- If WHERE clause were omitted, ALL employees would get salary = 85000!

The Single Most Dangerous SQL Statement

UPDATE without WHERE affects ALL rows in the table. There is no confirmation prompt, no 'are you sure?' dialog. The database executes immediately. An UPDATE employees SET salary = 0; will set every employee's salary to zero in milliseconds. Always write and verify WHERE clauses before executing UPDATE statements.

Verifying UPDATE Scope Before Execution:

A critical safety practice is to preview the affected rows before running UPDATE:

update_preview.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
-- SAFE PRACTICE: Preview rows before updating
 
-- Step 1: Write SELECT with your WHERE clause first
SELECT employee_id, first_name, last_name, salary
FROM employees
WHERE department_id = 10 AND hire_date < '2020-01-01';
 
-- Review the results. Are these the rows you want to update?
-- This query returns 15 rows.
 
-- Step 2: Convert SELECT to UPDATE, reusing the same WHERE
UPDATE employees
SET salary = salary * 1.05  -- 5% raise
WHERE department_id = 10 AND hire_date < '2020-01-01';
 
-- This updates exactly the 15 rows you previewed
 
-- BONUS: Use transaction for additional safety
BEGIN TRANSACTION;
 
UPDATE employees
SET salary = salary * 1.05
WHERE department_id = 10 AND hire_date < '2020-01-01';
 
-- Review changes
SELECT employee_id, first_name, salary
FROM employees
WHERE department_id = 10 AND hire_date < '2020-01-01';
 
-- If correct:
COMMIT;
-- If wrong:
-- ROLLBACK;

Single Column Updates

The simplest UPDATE modifies one column on selected rows. The new value can be a literal, an expression, a function result, or a value derived from other columns.

Literal Value Assignment:

update_literals.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
-- Update with literal values
 
-- Set a specific value
UPDATE products
SET unit_price = 29.99
WHERE product_id = 101;
 
-- Update with different data types
UPDATE products SET discontinued = TRUE WHERE product_id = 102;        -- Boolean
UPDATE products SET description = 'Updated description' WHERE product_id = 103;  -- String
UPDATE products SET units_in_stock = 0 WHERE category = 'Discontinued';           -- Integer from condition
UPDATE orders SET order_date = DATE '2024-01-15' WHERE order_id = 5001;           -- Date literal
 
-- Set column to NULL (if column allows NULL)
UPDATE employees
SET middle_name = NULL
WHERE employee_id = 1001;
 
-- Set column to DEFAULT (requires column to have DEFAULT defined)
UPDATE products
SET last_modified = DEFAULT
WHERE product_id = 101;
-- If last_modified has DEFAULT CURRENT_TIMESTAMP, it resets to NOW

Expression-Based Updates:

The SET clause can use expressions that compute the new value based on current column values:

update_expressions.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
-- Update using the column's current value
 
-- Increase price by 10%
UPDATE products
SET unit_price = unit_price * 1.10
WHERE category = 'Electronics';
 
-- Decrease stock (after order fulfillment)
UPDATE products
SET units_in_stock = units_in_stock - 5
WHERE product_id = 101;
 
-- Increment a counter
UPDATE articles
SET view_count = view_count + 1
WHERE article_id = 42;
 
-- String manipulation
UPDATE employees
SET email = LOWER(email)  -- Normalize to lowercase
WHERE email != LOWER(email);
 
UPDATE products
SET product_name = TRIM(product_name)  -- Remove leading/trailing spaces
WHERE product_name != TRIM(product_name);
 
-- Date arithmetic
UPDATE subscriptions
SET expiry_date = expiry_date + INTERVAL '30 days'
WHERE subscription_type = 'monthly' AND renewed = TRUE;
 
-- Using built-in functions
UPDATE audit_log
SET processed_at = CURRENT_TIMESTAMP
WHERE processed_at IS NULL AND created_at < CURRENT_DATE - INTERVAL '1 day';

Self-Referential Safe Updates

When updating a column based on its own value (like salary = salary * 1.1), the database reads the old value, computes the new value, then writes. This is atomic per row. You don't need to worry about the update affecting itself mid-computation.

Multiple Column Updates

A single UPDATE can modify multiple columns simultaneously. All assignments in the SET clause are evaluated using the row's original values, then applied together.

Multi-Column SET Syntax:

update_multiple_columns.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
-- Update multiple columns in one statement
UPDATE employees
SET 
    salary = 95000.00,
    department_id = 20,
    title = 'Senior Developer',
    last_modified = CURRENT_TIMESTAMP
WHERE employee_id = 1001;
 
-- All four columns updated in a single atomic operation
 
-- Real-world example: User profile update
UPDATE users
SET
    display_name = 'John Smith',
    email = 'john.smith@newdomain.com',
    bio = 'Software engineer with 10 years experience',
    avatar_url = 'https://example.com/avatars/john.jpg',
    updated_at = CURRENT_TIMESTAMP
WHERE user_id = 42;
 
-- Order status change with multiple related updates
UPDATE orders
SET
    status = 'shipped',
    shipped_date = CURRENT_DATE,
    tracking_number = 'TRACK123456789',
    updated_by = 'system',
    updated_at = CURRENT_TIMESTAMP
WHERE order_id = 5001 AND status = 'processing';

Column Interdependency in Multi-Column Updates:

A subtle but critical concept: all expressions in SET are evaluated using the row's values before any updates are applied. Column assignments don't chain within the same statement.

update_evaluation_order.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
-- Understanding SET evaluation order
 
-- Suppose we have: balance = 100, previous_balance = 50
 
-- ATTEMPT: Save old balance before updating new balance
UPDATE accounts
SET
    previous_balance = balance,      -- Sets previous_balance = 100 (original)
    balance = balance + 50           -- Sets balance = 150 (from original 100)
WHERE account_id = 1;
 
-- Result: previous_balance = 100, balance = 150  ✓ CORRECT!
 
-- Both assignments use the ORIGINAL row values
-- previous_balance gets the original balance (100)
-- balance gets original balance + 50 (100 + 50 = 150)
 
-- This is different from procedural code where:
-- previous_balance = balance  // previous_balance = 100
-- balance = balance + 50      // balance = 150
-- Would produce the same result, but for procedural reasons
 
-- SWAP example - this WORKS in SQL:
UPDATE accounts
SET
    balance = savings,
    savings = balance
WHERE account_id = 1;
 
-- If balance = 100, savings = 200 before update:
-- After update: balance = 200, savings = 100
-- Both assignments read original values, so the swap works!

Atomic Multi-Column Updates

Multi-column updates are atomic at the row level. Either all column changes succeed, or none do. This ensures data consistency—you won't end up with status = 'shipped' but shipped_date = NULL because of a partial failure.

Conditional Updates with CASE

Sometimes different rows need different updates based on their current values. The CASE expression enables conditional logic within the SET clause, applying different values to different rows in a single statement.

CASE in SET Clause:

update_case_expressions.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
-- Conditional update using CASE expression
 
-- Tiered salary increases based on current salary
UPDATE employees
SET salary = CASE
    WHEN salary < 50000 THEN salary * 1.10     -- 10% raise for < 50K
    WHEN salary < 80000 THEN salary * 1.07     -- 7% raise for 50K-80K
    WHEN salary < 120000 THEN salary * 1.05    -- 5% raise for 80K-120K
    ELSE salary * 1.03                          -- 3% raise for >= 120K
END
WHERE department_id = 10;
 
-- Grade assignment based on score
UPDATE exam_results
SET grade = CASE
    WHEN score >= 90 THEN 'A'
    WHEN score >= 80 THEN 'B'
    WHEN score >= 70 THEN 'C'
    WHEN score >= 60 THEN 'D'
    ELSE 'F'
END
WHERE exam_id = 101 AND grade IS NULL;
 
-- Status transition with validation
UPDATE orders
SET status = CASE
    WHEN status = 'pending' AND payment_confirmed = TRUE THEN 'processing'
    WHEN status = 'processing' AND items_packed = TRUE THEN 'ready_to_ship'
    WHEN status = 'ready_to_ship' AND tracking_number IS NOT NULL THEN 'shipped'
    ELSE status  -- No change if conditions not met
END
WHERE order_date < CURRENT_DATE - INTERVAL '1 day';

Multiple Columns with Independent Conditions:

Each column in SET can have its own CASE expression with different conditions:

update_multiple_case.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
-- Update multiple columns with independent CASE logic
 
UPDATE products
SET
    -- Price adjustment based on stock levels
    unit_price = CASE
        WHEN units_in_stock = 0 THEN unit_price * 1.20    -- Premium for out-of-stock
        WHEN units_in_stock < 10 THEN unit_price * 1.10  -- Increase for low stock
        WHEN units_in_stock > 100 THEN unit_price * 0.90 -- Discount for overstock
        ELSE unit_price
    END,
    
    -- Priority based on sales and stock
    reorder_priority = CASE
        WHEN units_in_stock = 0 THEN 'critical'
        WHEN units_in_stock < reorder_level THEN 'high'
        WHEN units_in_stock < reorder_level * 2 THEN 'medium'
        ELSE 'low'
    END,
    
    -- Visibility flag
    is_featured = CASE
        WHEN units_in_stock > 50 AND rating >= 4.5 THEN TRUE
        ELSE FALSE
    END,
    
    -- Always update modification timestamp
    last_modified = CURRENT_TIMESTAMP
    
WHERE category_id IN (1, 2, 3);

CASE with ELSE for Safety

Always include an ELSE clause in CASE expressions used for updates. Without ELSE, unmatched conditions result in NULL, which might not be what you want. Using ELSE with the original column value (ELSE status) keeps unmatched rows unchanged.

UPDATE with Subqueries

Subqueries in UPDATE statements enable updates based on data from other tables or complex computations. This is essential for maintaining denormalized summaries, propagating changes, and data synchronization.

Subquery in SET Clause:

update_subquery_set.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
-- Subquery in SET clause (scalar subquery - must return single value)
 
-- Update product price to match competitor's price
UPDATE products
SET unit_price = (
    SELECT MIN(competitor_price)
    FROM competitor_prices
    WHERE competitor_prices.product_sku = products.sku
)
WHERE product_id = 101;
 
-- Update employee manager based on department
UPDATE employees e
SET manager_id = (
    SELECT manager_id 
    FROM departments d 
    WHERE d.department_id = e.department_id
)
WHERE e.manager_id IS NULL;
 
-- Update order total from line items
UPDATE orders
SET total_amount = (
    SELECT SUM(quantity * unit_price)
    FROM order_items
    WHERE order_items.order_id = orders.order_id
)
WHERE order_id = 5001;

Correlated Subqueries:

Correlated subqueries reference the outer UPDATE's row, enabling row-by-row computation:

update_correlated_subquery.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
-- Correlated subquery updates
 
-- Update each customer's total_orders count
UPDATE customers c
SET total_orders = (
    SELECT COUNT(*)
    FROM orders o
    WHERE o.customer_id = c.customer_id  -- Correlation: references outer row
);
 
-- Update product rating from reviews
UPDATE products p
SET 
    average_rating = (
        SELECT AVG(rating)
        FROM reviews r
        WHERE r.product_id = p.product_id
    ),
    review_count = (
        SELECT COUNT(*)
        FROM reviews r
        WHERE r.product_id = p.product_id
    )
WHERE category = 'Electronics';
 
-- Update inventory status based on sales velocity
UPDATE products p
SET stock_status = CASE
    WHEN units_in_stock = 0 THEN 'out_of_stock'
    WHEN units_in_stock < (
        SELECT AVG(quantity) * 7  -- Week's worth of average sales
        FROM order_items oi
        JOIN orders o ON oi.order_id = o.order_id
        WHERE oi.product_id = p.product_id
        AND o.order_date > CURRENT_DATE - INTERVAL '30 days'
    ) THEN 'low_stock'
    ELSE 'in_stock'
END;

Subquery in WHERE Clause:

Subqueries can also determine which rows to update:

update_subquery_where.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
-- Subquery in WHERE to select rows for update
 
-- Update prices for products with low sales
UPDATE products
SET unit_price = unit_price * 0.85  -- 15% discount
WHERE product_id IN (
    SELECT product_id
    FROM order_items
    GROUP BY product_id
    HAVING SUM(quantity) < 10  -- Fewer than 10 units sold
);
 
-- Deactivate customers with no recent orders
UPDATE customers
SET is_active = FALSE
WHERE customer_id NOT IN (
    SELECT DISTINCT customer_id
    FROM orders
    WHERE order_date > CURRENT_DATE - INTERVAL '1 year'
);
 
-- Mark orders as high-value based on customer tier
UPDATE orders
SET priority = 'high'
WHERE customer_id IN (
    SELECT customer_id
    FROM customers
    WHERE customer_tier = 'platinum'
);

Scalar Subquery Requirement

Subqueries in the SET clause must return exactly one value (scalar subquery). If the subquery returns multiple rows, the UPDATE fails with an error. Use aggregation functions (MAX, MIN, AVG) or LIMIT 1 to ensure a single result.

UPDATE with JOIN

Many databases support UPDATE with JOIN syntax, allowing updates based on data from joined tables. This is often more readable and efficient than correlated subqueries.

UPDATE with JOIN Syntax Variations:

update_join_postgres.sql

-- PostgreSQL: UPDATE ... FROM ... WHERE
 
-- Update product prices based on supplier price list
UPDATE products p
SET 
    unit_price = s.suggested_price,
    last_price_update = CURRENT_TIMESTAMP
FROM suppliers s
WHERE p.supplier_id = s.supplier_id
  AND s.suggested_price IS NOT NULL
  AND p.unit_price != s.suggested_price;
 
-- Update with multiple joined tables
UPDATE order_items oi
SET 
    discount_percent = c.loyalty_discount + pr.promo_discount,
    discount_applied = TRUE
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
LEFT JOIN promotions pr ON oi.product_id = pr.product_id
    AND CURRENT_DATE BETWEEN pr.start_date AND pr.end_date
WHERE oi.order_id = o.order_id
  AND oi.discount_applied = FALSE
  AND o.order_date = CURRENT_DATE;

Syntax Varies by Database

UPDATE with JOIN syntax differs significantly between database systems. PostgreSQL uses FROM, MySQL uses JOIN directly after the table name, and SQL Server uses FROM with a table alias. Always consult your specific database's documentation.

UPDATE Safety Practices

UPDATE statements can cause irreversible damage if executed incorrectly. Following safety practices prevents accidents and enables recovery when mistakes happen.

Essential UPDATE Safety Rules

•Always write WHERE first — Start your UPDATE by writing the WHERE clause, not SET. This forces you to think about scope before modification.
•Preview with SELECT — Convert your UPDATE to a SELECT with the same WHERE to see affected rows before committing.
•Use transactions — Wrap UPDATE in BEGIN/COMMIT so you can ROLLBACK if results are unexpected.
•Include LIMIT in development — Some databases support UPDATE ... LIMIT N. Use it during testing to limit damage.
•Check row count — After UPDATE, verify the affected row count matches expectations.
•Backup before bulk updates — For large updates, create a backup or snapshot first.

update_safety_workflow.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
-- SAFE UPDATE WORKFLOW
 
-- 1. Start a transaction
BEGIN TRANSACTION;
 
-- 2. Preview the rows that will be affected
SELECT employee_id, first_name, last_name, salary, department_id
FROM employees
WHERE department_id = 10 AND hire_date < '2020-01-01';
-- Result: 15 rows
 
-- 3. Execute the UPDATE
UPDATE employees
SET salary = salary * 1.05
WHERE department_id = 10 AND hire_date < '2020-01-01';
-- Output: "15 rows affected" — matches preview ✓
 
-- 4. Verify the changes
SELECT employee_id, first_name, last_name, salary, department_id
FROM employees
WHERE department_id = 10 AND hire_date < '2020-01-01';
-- Verify salaries are updated correctly
 
-- 5. Commit if correct, rollback if not
COMMIT;  -- or ROLLBACK; if something is wrong
 
-- =====================================================
 
-- CREATING BACKUP BEFORE BULK UPDATE
-- Create a snapshot of data before modifying
CREATE TABLE employees_backup_20240115 AS
SELECT * FROM employees;
 
-- Now safe to perform bulk update
UPDATE employees
SET department_id = 20
WHERE department_id = 10 AND office_location = 'Building A';
 
-- If something goes wrong, restore from backup:
-- UPDATE employees e
-- SET salary = b.salary, department_id = b.department_id, ...
-- FROM employees_backup_20240115 b
-- WHERE e.employee_id = b.employee_id;

Production UPDATE Checklist

Before executing UPDATE in production: (1) Have you tested the exact query in a non-production environment? (2) Do you have a backup or rollback plan? (3) Have you previewed the affected rows with SELECT? (4) Is the row count reasonable? (5) Are you using a transaction? Never run production UPDATEs without these checks.

Returning Updated Data

Just as with INSERT, you often need to see the results of an UPDATE without a separate SELECT query. Modern databases provide mechanisms to return updated row data.

PostgreSQL RETURNING Clause:

update_returning.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
-- PostgreSQL: RETURNING after UPDATE
 
-- Get updated values
UPDATE products
SET unit_price = unit_price * 1.10
WHERE category = 'Electronics'
RETURNING product_id, product_name, unit_price AS new_price;
 
-- Return both old-style computed and new values
UPDATE employees
SET salary = salary * 1.05
WHERE employee_id = 1001
RETURNING 
    employee_id,
    first_name,
    salary AS new_salary,
    salary / 1.05 AS old_salary;  -- Compute original from new
 
-- SQL Server: OUTPUT clause with INSERTED and DELETED
UPDATE products
SET unit_price = unit_price * 1.10
OUTPUT 
    DELETED.product_id,
    DELETED.unit_price AS old_price,
    INSERTED.unit_price AS new_price
WHERE category = 'Electronics';
 
-- OUTPUT gives access to both old (DELETED) and new (INSERTED) values

Audit Trail with OUTPUT/RETURNING

The OUTPUT clause in SQL Server is particularly powerful because it provides both old and new values. This enables audit logging in a single statement—insert the OUTPUT results directly into an audit table.

Summary: Mastering UPDATE

The UPDATE statement is powerful but potentially dangerous. Mastering it means understanding both its capabilities and its risks.

Key Takeaways

•WHERE clause is critical — UPDATE without WHERE affects all rows. Always verify scope before execution.
•SET uses original values — All SET expressions evaluate using pre-update values, enabling safe swaps and self-referential updates.
•CASE enables conditional logic — Different rows can receive different updates in a single statement.
•Subqueries and JOINs extend power — Update based on data from other tables, but be mindful of scalar subquery requirements.
•Safety practices are non-negotiable — Preview with SELECT, use transactions, verify row counts, and backup before bulk updates.
•RETURNING/OUTPUT captures results — Get updated data without a separate SELECT query.

What's Next:

Having covered adding (INSERT) and modifying (UPDATE) data, the next page addresses the DELETE statement—removing data from tables while maintaining referential integrity.

Page Complete

You now have comprehensive knowledge of the UPDATE statement. From simple value assignments to complex conditional updates with joins, you can modify data efficiently and safely. Remember: with great power comes great responsibility—always preview, always verify, always backup.

2 / 5

Loading learning content...

Database Management SystemsSQL Fundamentals

Data Manipulation Language (DML)

LevelBeginner

Duration60 mins

TopicSQL Fundamentals

2 / 5

UPDATE Statement

Modifying Data in Place

What You Will Learn

UPDATE Fundamentals

The UPDATE statement modifies existing rows in a table. Its power lies in the combination of the SET clause (what to change) and the WHERE clause (which rows to change).

The Basic UPDATE Syntax:

update_basic_syntax.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
-- Complete UPDATE syntax pattern
UPDATE table_name
SET column1 = value1,
    column2 = value2,
    column3 = value3
WHERE condition;
 
-- The UPDATE statement has three key components:
-- 1. Target table: The table containing rows to modify
-- 2. SET clause: Column assignments specifying new values
-- 3. WHERE clause: Predicate identifying which rows to update (CRITICAL!)
 
-- Example: Update a single employee's salary
UPDATE employees
SET salary = 85000.00
WHERE employee_id = 1001;
 
-- This updates ONLY the row where employee_id = 1001
-- If WHERE clause were omitted, ALL employees would get salary = 85000!

The Single Most Dangerous SQL Statement

Verifying UPDATE Scope Before Execution:

A critical safety practice is to preview the affected rows before running UPDATE:

update_preview.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
-- SAFE PRACTICE: Preview rows before updating
 
-- Step 1: Write SELECT with your WHERE clause first
SELECT employee_id, first_name, last_name, salary
FROM employees
WHERE department_id = 10 AND hire_date < '2020-01-01';
 
-- Review the results. Are these the rows you want to update?
-- This query returns 15 rows.
 
-- Step 2: Convert SELECT to UPDATE, reusing the same WHERE
UPDATE employees
SET salary = salary * 1.05  -- 5% raise
WHERE department_id = 10 AND hire_date < '2020-01-01';
 
-- This updates exactly the 15 rows you previewed
 
-- BONUS: Use transaction for additional safety
BEGIN TRANSACTION;
 
UPDATE employees
SET salary = salary * 1.05
WHERE department_id = 10 AND hire_date < '2020-01-01';
 
-- Review changes
SELECT employee_id, first_name, salary
FROM employees
WHERE department_id = 10 AND hire_date < '2020-01-01';
 
-- If correct:
COMMIT;
-- If wrong:
-- ROLLBACK;

Single Column Updates

The simplest UPDATE modifies one column on selected rows. The new value can be a literal, an expression, a function result, or a value derived from other columns.

Literal Value Assignment:

update_literals.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
-- Update with literal values
 
-- Set a specific value
UPDATE products
SET unit_price = 29.99
WHERE product_id = 101;
 
-- Update with different data types
UPDATE products SET discontinued = TRUE WHERE product_id = 102;        -- Boolean
UPDATE products SET description = 'Updated description' WHERE product_id = 103;  -- String
UPDATE products SET units_in_stock = 0 WHERE category = 'Discontinued';           -- Integer from condition
UPDATE orders SET order_date = DATE '2024-01-15' WHERE order_id = 5001;           -- Date literal
 
-- Set column to NULL (if column allows NULL)
UPDATE employees
SET middle_name = NULL
WHERE employee_id = 1001;
 
-- Set column to DEFAULT (requires column to have DEFAULT defined)
UPDATE products
SET last_modified = DEFAULT
WHERE product_id = 101;
-- If last_modified has DEFAULT CURRENT_TIMESTAMP, it resets to NOW

Expression-Based Updates:

The SET clause can use expressions that compute the new value based on current column values:

update_expressions.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
-- Update using the column's current value
 
-- Increase price by 10%
UPDATE products
SET unit_price = unit_price * 1.10
WHERE category = 'Electronics';
 
-- Decrease stock (after order fulfillment)
UPDATE products
SET units_in_stock = units_in_stock - 5
WHERE product_id = 101;
 
-- Increment a counter
UPDATE articles
SET view_count = view_count + 1
WHERE article_id = 42;
 
-- String manipulation
UPDATE employees
SET email = LOWER(email)  -- Normalize to lowercase
WHERE email != LOWER(email);
 
UPDATE products
SET product_name = TRIM(product_name)  -- Remove leading/trailing spaces
WHERE product_name != TRIM(product_name);
 
-- Date arithmetic
UPDATE subscriptions
SET expiry_date = expiry_date + INTERVAL '30 days'
WHERE subscription_type = 'monthly' AND renewed = TRUE;
 
-- Using built-in functions
UPDATE audit_log
SET processed_at = CURRENT_TIMESTAMP
WHERE processed_at IS NULL AND created_at < CURRENT_DATE - INTERVAL '1 day';

Self-Referential Safe Updates

Multiple Column Updates

A single UPDATE can modify multiple columns simultaneously. All assignments in the SET clause are evaluated using the row's original values, then applied together.

Multi-Column SET Syntax:

update_multiple_columns.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
-- Update multiple columns in one statement
UPDATE employees
SET 
    salary = 95000.00,
    department_id = 20,
    title = 'Senior Developer',
    last_modified = CURRENT_TIMESTAMP
WHERE employee_id = 1001;
 
-- All four columns updated in a single atomic operation
 
-- Real-world example: User profile update
UPDATE users
SET
    display_name = 'John Smith',
    email = 'john.smith@newdomain.com',
    bio = 'Software engineer with 10 years experience',
    avatar_url = 'https://example.com/avatars/john.jpg',
    updated_at = CURRENT_TIMESTAMP
WHERE user_id = 42;
 
-- Order status change with multiple related updates
UPDATE orders
SET
    status = 'shipped',
    shipped_date = CURRENT_DATE,
    tracking_number = 'TRACK123456789',
    updated_by = 'system',
    updated_at = CURRENT_TIMESTAMP
WHERE order_id = 5001 AND status = 'processing';

Column Interdependency in Multi-Column Updates:

A subtle but critical concept: all expressions in SET are evaluated using the row's values before any updates are applied. Column assignments don't chain within the same statement.

update_evaluation_order.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
-- Understanding SET evaluation order
 
-- Suppose we have: balance = 100, previous_balance = 50
 
-- ATTEMPT: Save old balance before updating new balance
UPDATE accounts
SET
    previous_balance = balance,      -- Sets previous_balance = 100 (original)
    balance = balance + 50           -- Sets balance = 150 (from original 100)
WHERE account_id = 1;
 
-- Result: previous_balance = 100, balance = 150  ✓ CORRECT!
 
-- Both assignments use the ORIGINAL row values
-- previous_balance gets the original balance (100)
-- balance gets original balance + 50 (100 + 50 = 150)
 
-- This is different from procedural code where:
-- previous_balance = balance  // previous_balance = 100
-- balance = balance + 50      // balance = 150
-- Would produce the same result, but for procedural reasons
 
-- SWAP example - this WORKS in SQL:
UPDATE accounts
SET
    balance = savings,
    savings = balance
WHERE account_id = 1;
 
-- If balance = 100, savings = 200 before update:
-- After update: balance = 200, savings = 100
-- Both assignments read original values, so the swap works!

Atomic Multi-Column Updates

Conditional Updates with CASE

CASE in SET Clause:

update_case_expressions.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
-- Conditional update using CASE expression
 
-- Tiered salary increases based on current salary
UPDATE employees
SET salary = CASE
    WHEN salary < 50000 THEN salary * 1.10     -- 10% raise for < 50K
    WHEN salary < 80000 THEN salary * 1.07     -- 7% raise for 50K-80K
    WHEN salary < 120000 THEN salary * 1.05    -- 5% raise for 80K-120K
    ELSE salary * 1.03                          -- 3% raise for >= 120K
END
WHERE department_id = 10;
 
-- Grade assignment based on score
UPDATE exam_results
SET grade = CASE
    WHEN score >= 90 THEN 'A'
    WHEN score >= 80 THEN 'B'
    WHEN score >= 70 THEN 'C'
    WHEN score >= 60 THEN 'D'
    ELSE 'F'
END
WHERE exam_id = 101 AND grade IS NULL;
 
-- Status transition with validation
UPDATE orders
SET status = CASE
    WHEN status = 'pending' AND payment_confirmed = TRUE THEN 'processing'
    WHEN status = 'processing' AND items_packed = TRUE THEN 'ready_to_ship'
    WHEN status = 'ready_to_ship' AND tracking_number IS NOT NULL THEN 'shipped'
    ELSE status  -- No change if conditions not met
END
WHERE order_date < CURRENT_DATE - INTERVAL '1 day';

Multiple Columns with Independent Conditions:

Each column in SET can have its own CASE expression with different conditions:

update_multiple_case.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
-- Update multiple columns with independent CASE logic
 
UPDATE products
SET
    -- Price adjustment based on stock levels
    unit_price = CASE
        WHEN units_in_stock = 0 THEN unit_price * 1.20    -- Premium for out-of-stock
        WHEN units_in_stock < 10 THEN unit_price * 1.10  -- Increase for low stock
        WHEN units_in_stock > 100 THEN unit_price * 0.90 -- Discount for overstock
        ELSE unit_price
    END,
    
    -- Priority based on sales and stock
    reorder_priority = CASE
        WHEN units_in_stock = 0 THEN 'critical'
        WHEN units_in_stock < reorder_level THEN 'high'
        WHEN units_in_stock < reorder_level * 2 THEN 'medium'
        ELSE 'low'
    END,
    
    -- Visibility flag
    is_featured = CASE
        WHEN units_in_stock > 50 AND rating >= 4.5 THEN TRUE
        ELSE FALSE
    END,
    
    -- Always update modification timestamp
    last_modified = CURRENT_TIMESTAMP
    
WHERE category_id IN (1, 2, 3);

CASE with ELSE for Safety

UPDATE with Subqueries

Subquery in SET Clause:

update_subquery_set.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
-- Subquery in SET clause (scalar subquery - must return single value)
 
-- Update product price to match competitor's price
UPDATE products
SET unit_price = (
    SELECT MIN(competitor_price)
    FROM competitor_prices
    WHERE competitor_prices.product_sku = products.sku
)
WHERE product_id = 101;
 
-- Update employee manager based on department
UPDATE employees e
SET manager_id = (
    SELECT manager_id 
    FROM departments d 
    WHERE d.department_id = e.department_id
)
WHERE e.manager_id IS NULL;
 
-- Update order total from line items
UPDATE orders
SET total_amount = (
    SELECT SUM(quantity * unit_price)
    FROM order_items
    WHERE order_items.order_id = orders.order_id
)
WHERE order_id = 5001;

Correlated Subqueries:

Correlated subqueries reference the outer UPDATE's row, enabling row-by-row computation:

update_correlated_subquery.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
-- Correlated subquery updates
 
-- Update each customer's total_orders count
UPDATE customers c
SET total_orders = (
    SELECT COUNT(*)
    FROM orders o
    WHERE o.customer_id = c.customer_id  -- Correlation: references outer row
);
 
-- Update product rating from reviews
UPDATE products p
SET 
    average_rating = (
        SELECT AVG(rating)
        FROM reviews r
        WHERE r.product_id = p.product_id
    ),
    review_count = (
        SELECT COUNT(*)
        FROM reviews r
        WHERE r.product_id = p.product_id
    )
WHERE category = 'Electronics';
 
-- Update inventory status based on sales velocity
UPDATE products p
SET stock_status = CASE
    WHEN units_in_stock = 0 THEN 'out_of_stock'
    WHEN units_in_stock < (
        SELECT AVG(quantity) * 7  -- Week's worth of average sales
        FROM order_items oi
        JOIN orders o ON oi.order_id = o.order_id
        WHERE oi.product_id = p.product_id
        AND o.order_date > CURRENT_DATE - INTERVAL '30 days'
    ) THEN 'low_stock'
    ELSE 'in_stock'
END;

Subquery in WHERE Clause:

Subqueries can also determine which rows to update:

update_subquery_where.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
-- Subquery in WHERE to select rows for update
 
-- Update prices for products with low sales
UPDATE products
SET unit_price = unit_price * 0.85  -- 15% discount
WHERE product_id IN (
    SELECT product_id
    FROM order_items
    GROUP BY product_id
    HAVING SUM(quantity) < 10  -- Fewer than 10 units sold
);
 
-- Deactivate customers with no recent orders
UPDATE customers
SET is_active = FALSE
WHERE customer_id NOT IN (
    SELECT DISTINCT customer_id
    FROM orders
    WHERE order_date > CURRENT_DATE - INTERVAL '1 year'
);
 
-- Mark orders as high-value based on customer tier
UPDATE orders
SET priority = 'high'
WHERE customer_id IN (
    SELECT customer_id
    FROM customers
    WHERE customer_tier = 'platinum'
);

Scalar Subquery Requirement

UPDATE with JOIN

Many databases support UPDATE with JOIN syntax, allowing updates based on data from joined tables. This is often more readable and efficient than correlated subqueries.

UPDATE with JOIN Syntax Variations:

update_join_postgres.sql

-- PostgreSQL: UPDATE ... FROM ... WHERE
 
-- Update product prices based on supplier price list
UPDATE products p
SET 
    unit_price = s.suggested_price,
    last_price_update = CURRENT_TIMESTAMP
FROM suppliers s
WHERE p.supplier_id = s.supplier_id
  AND s.suggested_price IS NOT NULL
  AND p.unit_price != s.suggested_price;
 
-- Update with multiple joined tables
UPDATE order_items oi
SET 
    discount_percent = c.loyalty_discount + pr.promo_discount,
    discount_applied = TRUE
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
LEFT JOIN promotions pr ON oi.product_id = pr.product_id
    AND CURRENT_DATE BETWEEN pr.start_date AND pr.end_date
WHERE oi.order_id = o.order_id
  AND oi.discount_applied = FALSE
  AND o.order_date = CURRENT_DATE;

Syntax Varies by Database

UPDATE Safety Practices

UPDATE statements can cause irreversible damage if executed incorrectly. Following safety practices prevents accidents and enables recovery when mistakes happen.

Essential UPDATE Safety Rules

•Always write WHERE first — Start your UPDATE by writing the WHERE clause, not SET. This forces you to think about scope before modification.
•Preview with SELECT — Convert your UPDATE to a SELECT with the same WHERE to see affected rows before committing.
•Use transactions — Wrap UPDATE in BEGIN/COMMIT so you can ROLLBACK if results are unexpected.
•Include LIMIT in development — Some databases support UPDATE ... LIMIT N. Use it during testing to limit damage.
•Check row count — After UPDATE, verify the affected row count matches expectations.
•Backup before bulk updates — For large updates, create a backup or snapshot first.

update_safety_workflow.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
-- SAFE UPDATE WORKFLOW
 
-- 1. Start a transaction
BEGIN TRANSACTION;
 
-- 2. Preview the rows that will be affected
SELECT employee_id, first_name, last_name, salary, department_id
FROM employees
WHERE department_id = 10 AND hire_date < '2020-01-01';
-- Result: 15 rows
 
-- 3. Execute the UPDATE
UPDATE employees
SET salary = salary * 1.05
WHERE department_id = 10 AND hire_date < '2020-01-01';
-- Output: "15 rows affected" — matches preview ✓
 
-- 4. Verify the changes
SELECT employee_id, first_name, last_name, salary, department_id
FROM employees
WHERE department_id = 10 AND hire_date < '2020-01-01';
-- Verify salaries are updated correctly
 
-- 5. Commit if correct, rollback if not
COMMIT;  -- or ROLLBACK; if something is wrong
 
-- =====================================================
 
-- CREATING BACKUP BEFORE BULK UPDATE
-- Create a snapshot of data before modifying
CREATE TABLE employees_backup_20240115 AS
SELECT * FROM employees;
 
-- Now safe to perform bulk update
UPDATE employees
SET department_id = 20
WHERE department_id = 10 AND office_location = 'Building A';
 
-- If something goes wrong, restore from backup:
-- UPDATE employees e
-- SET salary = b.salary, department_id = b.department_id, ...
-- FROM employees_backup_20240115 b
-- WHERE e.employee_id = b.employee_id;

Production UPDATE Checklist

Returning Updated Data

Just as with INSERT, you often need to see the results of an UPDATE without a separate SELECT query. Modern databases provide mechanisms to return updated row data.

PostgreSQL RETURNING Clause:

update_returning.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
-- PostgreSQL: RETURNING after UPDATE
 
-- Get updated values
UPDATE products
SET unit_price = unit_price * 1.10
WHERE category = 'Electronics'
RETURNING product_id, product_name, unit_price AS new_price;
 
-- Return both old-style computed and new values
UPDATE employees
SET salary = salary * 1.05
WHERE employee_id = 1001
RETURNING 
    employee_id,
    first_name,
    salary AS new_salary,
    salary / 1.05 AS old_salary;  -- Compute original from new
 
-- SQL Server: OUTPUT clause with INSERTED and DELETED
UPDATE products
SET unit_price = unit_price * 1.10
OUTPUT 
    DELETED.product_id,
    DELETED.unit_price AS old_price,
    INSERTED.unit_price AS new_price
WHERE category = 'Electronics';
 
-- OUTPUT gives access to both old (DELETED) and new (INSERTED) values

Audit Trail with OUTPUT/RETURNING

Summary: Mastering UPDATE

The UPDATE statement is powerful but potentially dangerous. Mastering it means understanding both its capabilities and its risks.

Key Takeaways

•WHERE clause is critical — UPDATE without WHERE affects all rows. Always verify scope before execution.
•SET uses original values — All SET expressions evaluate using pre-update values, enabling safe swaps and self-referential updates.
•CASE enables conditional logic — Different rows can receive different updates in a single statement.
•Subqueries and JOINs extend power — Update based on data from other tables, but be mindful of scalar subquery requirements.
•Safety practices are non-negotiable — Preview with SELECT, use transactions, verify row counts, and backup before bulk updates.
•RETURNING/OUTPUT captures results — Get updated data without a separate SELECT query.

What's Next:

Having covered adding (INSERT) and modifying (UPDATE) data, the next page addresses the DELETE statement—removing data from tables while maintaining referential integrity.

Page Complete

2 / 5