Update_data

Chapter 10: UPDATE - Modifying Data

Updating Existing Records in PostgreSQL

---

10.1 UPDATE Fundamentals

The UPDATE statement modifies existing rows in a table. Understanding how UPDATE works with MVCC and returning updated values is crucial.

                    UPDATE Query Processing
    ========================================================================

    ┌─────────────────────────────────────────────────────────────────────┐
    │                      UPDATE Statement Flow                          │
    │                                                                      │
    │  UPDATE table_name                                                  │
    │  SET column1 = value1,                                              │
    │      column2 = value2                                              │
    │  WHERE condition                                                   │
    │  RETURNING *;                                                      │
    │                                                                      │
    │  Processing Order:                                                  │
    │  1. FROM (find table)                                              │
    │  2. WHERE (identify rows)                                         │
    │  3. SET (modify columns)                                          │
    │  4. RETURNING (return changes)                                     │
    │                                                                      │
    └─────────────────────────────────────────────────────────────────────┘

---

10.2 Basic UPDATE Operations

-- Create sample table
CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(255),
    salary DECIMAL(10,2),
    department VARCHAR(50),
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO employees (name, email, salary, department) VALUES
    ('John Smith', 'john@company.com', 50000, 'Engineering'),
    ('Jane Doe', 'jane@company.com', 60000, 'Sales'),
    ('Bob Wilson', 'bob@company.com', 55000, 'Engineering'),
    ('Alice Brown', 'alice@company.com', 65000, 'Marketing');

-- Basic UPDATE
UPDATE employees SET salary = 55000 WHERE id = 1;

-- Update multiple columns
UPDATE employees
SET salary = 70000,
    department = 'Senior Engineering'
WHERE id = 1;

-- UPDATE with WHERE subquery
UPDATE employees
SET salary = salary * 1.1
WHERE department = 'Engineering'
RETURNING id, name, salary;

---

10.3 UPDATE with Expressions

-- Arithmetic updates
UPDATE employees SET salary = salary * 1.05;  -- 5% raise for all

-- Increment
UPDATE employees SET salary = salary + 5000 WHERE id = 2;

-- String concatenation
UPDATE employees SET email = LOWER(email);

-- Using CASE for conditional updates
UPDATE employees SET
    salary = CASE
        WHEN department = 'Engineering' THEN salary * 1.10
        WHEN department = 'Sales' THEN salary * 1.08
        ELSE salary * 1.05
    END;

-- Multiple columns with expressions
UPDATE products SET
    price = price * 1.1,
    stock = stock - 1,
    updated_at = CURRENT_TIMESTAMP
WHERE id = 1;

-- Update using another table (UPDATE ... FROM)
UPDATE employees e SET
    salary = e.salary * 1.1
FROM departments d
WHERE e.department = d.name
  AND d.budget > 100000;

---

10.4 UPDATE with RETURNING

-- Return updated values
UPDATE employees SET salary = 75000 WHERE id = 1
RETURNING id, name, salary, department;

-- Return affected rows count
UPDATE employees SET is_active = FALSE
WHERE salary < 50000
RETURNING id, name;

-- Use with application logic
-- The UPDATE statement returns the rows that were modified

-- Update and return old values
UPDATE employees SET salary = 80000 WHERE id = 1
RETURNING id, name, salary AS old_salary, 80000 AS new_salary;

-- Combining with other operations
UPDATE employees SET
    salary = salary + 5000,
    department = 'Management'
WHERE id = (SELECT id FROM employees ORDER BY salary DESC LIMIT 1)
RETURNING *;

---

10.5 UPDATE with Subqueries

-- Update based on subquery
UPDATE employees e SET
    salary = (
        SELECT AVG(salary) * 1.1
        FROM employees
        WHERE department = e.department
    )
WHERE e.salary < (
    SELECT AVG(salary)
    FROM employees
    WHERE department = e.department
);

-- Using EXISTS
UPDATE employees e SET
    department = 'Senior Staff'
WHERE EXISTS (
    SELECT 1
    FROM performance_reviews pr
    WHERE pr.employee_id = e.id
    AND pr.rating = 'excellent'
);

-- Update using JOIN
UPDATE employees e SET
    salary = e.salary * 1.1
FROM departments d
WHERE e.department = d.name
AND d.is_active = TRUE;

-- Update with multiple subqueries
UPDATE products p SET
    price = p.price * (SELECT discount FROM categories c WHERE c.id = p.category_id),
    stock = p.stock - (
        SELECT SUM(quantity) FROM order_items WHERE product_id = p.id
    )
WHERE p.stock > 0;

---

10.6 UPDATE with CTEs (Common Table Expressions)

-- Using CTE to identify rows to update
WITH high_earners AS (
    SELECT id FROM employees WHERE salary > 70000
)
UPDATE employees SET department = 'Executive'
WHERE id IN (SELECT id FROM high_earners)
RETURNING id, name, department;

-- Complex update with CTE
WITH salary_stats AS (
    SELECT
        department,
        AVG(salary) as avg_salary,
        MAX(salary) as max_salary
    FROM employees
    GROUP BY department
)
UPDATE employees e SET
    salary = ss.avg_salary,
    department = CASE
        WHEN e.salary = ss.max_salary THEN e.department || ' (Lead)'
        ELSE e.department
    END
FROM salary_stats ss
WHERE e.department = ss.department
RETURNING e.id, e.name, e.salary as old_salary, ss.avg_salary as new_salary;

-- Update with recursive CTE
WITH RECURSIVE org_chart AS (
    -- Base case: top-level managers
    SELECT id, name, manager_id, 1 as level
    FROM employees WHERE manager_id IS NULL

    UNION ALL

    -- Recursive case
    SELECT e.id, e.name, e.manager_id, oc.level + 1
    FROM employees e
    JOIN org_chart oc ON e.manager_id = oc.id
    WHERE oc.level < 3
)
UPDATE employees SET is_active = TRUE
WHERE id IN (SELECT id FROM org_chart)
RETURNING id, name;

---

10.7 UPDATE with NULL Values

-- Set column to NULL
UPDATE employees SET email = NULL WHERE id = 1;

-- Update only NULL values
UPDATE employees SET department = 'Unassigned' WHERE department IS NULL;

-- Use COALESCE to preserve existing values
UPDATE employees SET
    email = COALESCE(email, 'unknown@company.com'),
    department = COALESCE(department, 'General');

-- Conditional NULL update
UPDATE employees SET
    terminated_at = CASE
        WHEN is_active = FALSE THEN CURRENT_TIMESTAMP
        ELSE NULL
    END;

---

10.8 UPDATE with ON CONFLICT

-- Create table with unique constraint
CREATE TABLE inventory (
    product_id INTEGER PRIMARY KEY,
    product_name VARCHAR(200),
    quantity INTEGER DEFAULT 0,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- UPSERT: Update if exists, insert if not
INSERT INTO inventory (product_id, product_name, quantity)
VALUES (1, 'Widget', 100)
ON CONFLICT (product_id)
DO UPDATE SET
    product_name = EXCLUDED.product_name,
    quantity = inventory.quantity + EXCLUDED.quantity,
    updated_at = CURRENT_TIMESTAMP;

-- Update with DO NOTHING on conflict
INSERT INTO inventory (product_id, product_name, quantity)
VALUES (1, 'Widget', 50)
ON CONFLICT (product_id) DO NOTHING;

-- Multiple conflict targets
CREATE TABLE prices (
    product_id INTEGER,
    price_type VARCHAR(20),
    amount DECIMAL(10,2),
    PRIMARY KEY (product_id, price_type)
);

INSERT INTO prices (product_id, price_type, amount)
VALUES (1, 'retail', 99.99)
ON CONFLICT (product_id, price_type)
DO UPDATE SET amount = EXCLUDED.amount;

---

10.9 Batch UPDATE Operations

-- Update using array
UPDATE employees SET salary = salary * 1.05
WHERE id = ANY(ARRAY[1, 2, 3, 4, 5]);

-- Update using IN clause
UPDATE employees SET department = 'Training'
WHERE id IN (1, 2, 3, 4, 5);

-- Bulk update with conditions
UPDATE employees SET
    salary = CASE id
        WHEN 1 THEN 55000
        WHEN 2 THEN 65000
        WHEN 3 THEN 60000
        ELSE salary
    END
WHERE id IN (1, 2, 3);

-- Using LIMIT with ORDER BY
UPDATE employees
SET salary = 80000
FROM (
    SELECT id FROM employees
    ORDER BY salary DESC
    LIMIT 10
) top_earners
WHERE employees.id = top_earners.id;

-- Partition-safe updates
UPDATE orders SET status = 'archived', updated_at = NOW()
WHERE created_at < '2023-01-01'
AND status = 'completed';

---

10.10 Transactional UPDATE

-- UPDATE within transaction
BEGIN;

UPDATE accounts SET balance = balance - 100 WHERE user_id = 1;
UPDATE accounts SET balance = balance + 100 WHERE user_id = 2;

-- Check conditions
-- If something is wrong:
ROLLBACK;

-- If everything is good:
COMMIT;

-- UPDATE with savepoints
BEGIN;
    UPDATE employees SET salary = 50000 WHERE id = 1;
    SAVEPOINT sp1;

    UPDATE employees SET salary = 60000 WHERE id = 2;

    -- Undo second update
    ROLLBACK TO SAVEPOINT sp1;
COMMIT;

-- Using advisory locks for concurrent updates
SELECT pg_advisory_xact_lock(12345);  -- Lock ID
UPDATE products SET stock = stock - 1 WHERE id = 1;

---

10.11 Best Practices

                    UPDATE Best Practices
    ========================================================================

    1. Always Use WHERE Clause
       ┌──────────────────────────────────────────────────────────────┐
       │  ❌ UPDATE employees SET salary = 60000;                   │
       │     (Updates ALL rows!)                                     │
       │                                                              │
       │  ✓ UPDATE employees SET salary = 60000 WHERE id = 1;       │
       └──────────────────────────────────────────────────────────────┘

    2. Use RETURNING
       ┌──────────────────────────────────────────────────────────────┐
       │  • Get updated values without additional query             │
       │  • Useful for audit trails                                   │
       │  • Reduces application round trips                          │
       └──────────────────────────────────────────────────────────────┘

    3. Test UPDATE with SELECT First
       ┌──────────────────────────────────────────────────────────────┐
       │  -- Before running:                                         │
       │  SELECT * FROM employees WHERE <your conditions>;           │
       │  -- Then:                                                    │
       │  UPDATE employees SET ... WHERE <same conditions>;         │
       └──────────────────────────────────────────────────────────────┘

    4. Use Transactions for Multi-Table Updates
       ┌──────────────────────────────────────────────────────────────┐
       │  • Ensure data consistency                                  │
       │  • Easy rollback on errors                                   │
       └──────────────────────────────────────────────────────────────┘

    5. Index Foreign Keys
       ┌──────────────────────────────────────────────────────────────┐
       │  • Add indexes on columns used in WHERE conditions          │
       │  • Particularly important for JOIN conditions              │
       └──────────────────────────────────────────────────────────────┘
    ========================================================================

---

Next Chapter

Chapter 11: DELETE - Removing Data

---

Last Updated: February 2026