SQL UPDATE

Complete SQL UPDATE Technical Guide

1. Fundamental Syntax

UPDATE table_name
SET column1 = value1,
    column2 = value2
[WHERE conditions];

Key Notes:

WHERE clause is optional but critical for targeted updates
Can update multiple columns in one statement
Values can be literals, expressions, or subqueries

2. Advanced Update Patterns

Conditional Updates

UPDATE products
SET price = CASE
    WHEN stock > 100 THEN price * 0.9
    WHEN stock < 10 THEN price * 1.1
    ELSE price
  END;

From Subquery

UPDATE employees e
SET salary = (
  SELECT AVG(salary)
  FROM employees
  WHERE department = e.department
);

Join Updates

UPDATE orders o
JOIN customers c ON o.customer_id = c.id
SET o.priority = 'HIGH'
WHERE c.status = 'VIP';

Incremental Updates

UPDATE page_views
SET view_count = view_count + 1
WHERE page_id = 123;

3. Database-Specific Features

PostgreSQL

UPDATE ... RETURNING *;
-- Returns updated rows

FROM additional_tables
-- Powerful FROM clause

SQL Server

UPDATE TOP (100) table...
-- Limits rows updated

OUTPUT inserted.*
-- Captures changes

MySQL

UPDATE IGNORE table...
-- Skips errors

LIMIT row_count
-- Controls update volume

Oracle

RETURNING column INTO variable
-- Captures single value

MERGE statement
-- Upsert capability

4. Performance Considerations

Index Usage

WHERE clause should use indexed columns
Updating indexed columns is expensive
Consider disabling indexes during bulk updates

Locking Behavior

Row-level vs table-level locks
Transaction isolation levels matter
Long-running updates block other operations

Batch Updates

-- SQL Server pattern
WHILE EXISTS (SELECT 1 FROM table WHERE needs_update = 1)
BEGIN
  UPDATE TOP (1000) table...
END

CTE Updates

WITH targets AS (
  SELECT id FROM large_table
  WHERE condition LIMIT 10000
)
UPDATE large_table
SET column = value
FROM targets
WHERE large_table.id = targets.id;

5. Error Handling & Safety

Common Pitfalls

Missing WHERE clause updates all rows
Constraint violations (FK, CHECK, UNIQUE)
Data type conversion errors

Safety Measures

Use transactions for multi-statement updates
Test with SELECT first
Backup before large updates
Add LIMIT clauses for safety

Transaction Pattern

BEGIN TRANSACTION;

-- First verify with SELECT
SELECT * FROM table WHERE condition;

-- Then update
UPDATE table SET column = value WHERE condition;

-- Commit only after verification
COMMIT;
-- or ROLLBACK if issues found

6. Advanced Scenarios

JSON Updates

-- PostgreSQL
UPDATE users
SET profile = jsonb_set(profile, '{address,city}', '"New York"')
WHERE id = 123;

Temporal Tables

-- SQL Server
UPDATE Employees
SET Position = 'Manager'
WHERE EmployeeID = 123;
-- Automatically preserves history

Partitioned Tables

UPDATE sales PARTITION (p_2023)
SET status = 'archived'
WHERE sale_date BETWEEN '2023-01-01' AND '2023-12-31';

Update with Window Functions

WITH ranked AS (
  SELECT id, RANK() OVER (PARTITION BY dept ORDER BY sales DESC) as rnk
  FROM salespeople
)
UPDATE salespeople
SET bonus = CASE WHEN rnk = 1 THEN 5000 ELSE 2000 END
FROM ranked
WHERE salespeople.id = ranked.id;

Advanced UPDATE Topics

1. UPDATE Triggers

CREATE TRIGGER log_salary_changes
BEFORE UPDATE ON employees
FOR EACH ROW
BEGIN
  INSERT INTO salary_audit
  VALUES (OLD.employee_id, OLD.salary, NEW.salary, CURRENT_TIMESTAMP);
END;

BEFORE UPDATE: Executes before the update operation, can modify values
AFTER UPDATE: Executes after successful updates, often used for auditing
INSTEAD OF UPDATE: (For views) Replaces the standard update operation
Access to OLD/NEW values: OLD contains pre-update values, NEW contains proposed changes

2. Replication Impact

-- MySQL binary log format examples
SET binlog_format = 'ROW';  -- Logs changed rows only
SET binlog_format = 'STATEMENT';  -- Logs the actual UPDATE statement

Row-based replication: Efficient for large updates but larger log size
Statement-based replication: Compact logs but non-deterministic updates can cause drift
Conflict resolution: Multi-master replication requires strategies for concurrent updates
Latency considerations: Large updates may cause replication lag

3. Materialized View Refresh

-- PostgreSQL example
UPDATE inventory SET stock = stock - 10 WHERE product_id = 123;

-- Manual refresh needed for some materialized views
REFRESH MATERIALIZED VIEW CONCURRENTLY inventory_summary;

Immediate vs deferred refresh: Some views update automatically, others require manual refresh
Incremental refresh: Only recomputes affected portions of the view
Performance impact: Base table updates may slow down with many dependent views
Transaction boundaries: Some systems refresh views at transaction commit

4. Distributed Updates

-- Cassandra batch update example
BEGIN BATCH
  UPDATE users SET email = 'new@email.com' WHERE user_id = 1;
  UPDATE profiles SET verified = true WHERE user_id = 1;
APPLY BATCH;

Two-phase commit: Coordinated updates across shards with prepare/commit phases
Paxos protocols: For consensus in distributed systems
Shard routing: Updates must reach the correct partition
Eventual consistency: Some systems allow temporary inconsistencies
Conflict-free Replicated Data Types (CRDTs): Special data structures for distributed updates

5. UPDATE Execution Plans

-- PostgreSQL EXPLAIN output example
EXPLAIN UPDATE orders
SET status = 'processed'
WHERE order_date < '2023-01-01';

-- Typical plan:
Update on orders (cost=0.29..1254.32 rows=4231 width=86)
  -> Index Scan using idx_order_date on orders (cost=0.29..1254.32 rows=4231 width=86)
        Index Cond: (order_date < '2023-01-01'::date)

Read-modify-write cycle: How the planner breaks down the operation
Index usage: For WHERE clause vs updated columns
Join strategies: In multi-table updates
Statistics dependence: Cardinality estimates affect plan choice
Parallel update: Some databases can parallelize certain updates

6. Concurrency Control (MVCC)

-- PostgreSQL MVCC visibility example
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
-- Other transactions see old value until commit
COMMIT;

Version chains: How databases store multiple row versions
Transaction isolation levels: READ UNCOMMITTED to SERIALIZABLE
Write skew: Special concurrency issue with certain update patterns
Predicate locking: For SERIALIZABLE isolation
Hot spots: Contention on frequently updated rows