SQL UPDATE Statement

UPDATE modifies existing rows in a table. Unlike INSERT (which adds rows) or DELETE (which removes them), UPDATE changes the values in one or more columns for rows that match a condition.

Basic Syntax

UPDATE table_name
SET column1 = value1,
    column2 = value2
WHERE condition;

Always include a WHERE clause. Without one, every row in the table is updated:

-- DANGEROUS: updates every row
UPDATE products SET is_active = FALSE;

-- Safe: updates specific rows
UPDATE products
SET is_active = FALSE
WHERE last_sold_date < '2022-01-01';

Update a Single Row

The most common pattern — update one row by its primary key:

UPDATE customers
SET email = 'alice.smith@example.com',
    updated_at = NOW()
WHERE customer_id = 1042;

Update Multiple Columns

UPDATE products
SET
    price = 149.99,
    sale_price = 129.99,
    is_on_sale = TRUE,
    updated_at = NOW()
WHERE product_id = 8821;

Update Based on a Computed Value

Reference the column’s current value on the right side:

-- Apply a 10% discount to all electronics
UPDATE products
SET price = price * 0.90
WHERE category = 'electronics';

-- Increment a counter
UPDATE user_stats
SET login_count = login_count + 1,
    last_login_at = NOW()
WHERE user_id = 12345;

-- Cap a value (don't let it exceed a maximum)
UPDATE accounts
SET balance = LEAST(balance + 100, 10000)  -- add $100 but cap at $10,000
WHERE account_id = 555;

Preview Before Updating

Always run the equivalent SELECT first to confirm you’re targeting the right rows:

-- Preview: see what will be changed
SELECT product_id, name, price, category
FROM products
WHERE category = 'electronics' AND price > 500;

-- Then run the update
UPDATE products
SET price = price * 0.90
WHERE category = 'electronics' AND price > 500;

RETURNING: See What Changed (PostgreSQL)

RETURNING returns the updated rows — useful for auditing or chaining operations:

UPDATE orders
SET status = 'shipped',
    shipped_at = NOW()
WHERE order_id = 7771
RETURNING order_id, customer_id, status, shipped_at;

-- Update and capture in a CTE for further processing
WITH updated_orders AS (
    UPDATE orders
    SET status = 'archived'
    WHERE status = 'completed' AND created_at < '2024-01-01'
    RETURNING order_id, customer_id, amount
)
INSERT INTO orders_archive (order_id, customer_id, amount, archived_at)
SELECT order_id, customer_id, amount, NOW()
FROM updated_orders;

UPDATE with Subquery in SET

Use a subquery to set a column’s value from another table:

-- Update each product's category_name from the categories table
UPDATE products p
SET category_name = (
    SELECT name FROM categories c WHERE c.category_id = p.category_id
)
WHERE category_name IS NULL;

UPDATE with JOIN (MySQL and SQL Server)

-- MySQL: update using JOIN
UPDATE orders o
JOIN customers c ON o.customer_id = c.customer_id
SET o.shipping_address = c.default_address
WHERE o.shipping_address IS NULL;

-- SQL Server
UPDATE o
SET o.shipping_address = c.default_address
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
WHERE o.shipping_address IS NULL;

UPDATE Using FROM (PostgreSQL)

PostgreSQL uses FROM clause for join-based updates:

-- Update orders with customer's current email
UPDATE orders o
SET customer_email = c.email
FROM customers c
WHERE o.customer_id = c.customer_id
  AND o.customer_email IS NULL;

UPDATE with CTE (PostgreSQL)

For complex update logic, a CTE can prepare the data:

WITH price_adjustments AS (
    SELECT
        product_id,
        CASE
            WHEN stock_quantity > 1000 THEN price * 0.85
            WHEN stock_quantity > 500  THEN price * 0.90
            ELSE price
        END AS new_price
    FROM products
    WHERE is_on_sale = FALSE
)
UPDATE products p
SET price = pa.new_price,
    is_on_sale = TRUE,
    updated_at = NOW()
FROM price_adjustments pa
WHERE p.product_id = pa.product_id
  AND pa.new_price != p.price;

UPSERT: INSERT or UPDATE (INSERT ON CONFLICT)

Upsert inserts a row if it doesn’t exist, or updates it if it does:

-- PostgreSQL: UPSERT with ON CONFLICT
INSERT INTO product_inventory (product_id, quantity, updated_at)
VALUES (8821, 150, NOW())
ON CONFLICT (product_id) DO UPDATE
    SET quantity = EXCLUDED.quantity,
        updated_at = EXCLUDED.updated_at;

-- MySQL: ON DUPLICATE KEY UPDATE
INSERT INTO product_inventory (product_id, quantity, updated_at)
VALUES (8821, 150, NOW())
ON DUPLICATE KEY UPDATE
    quantity = VALUES(quantity),
    updated_at = NOW();

EXCLUDED in PostgreSQL refers to the row that was proposed for insertion but conflicted.

Safe UPDATE Patterns in Production

Wrap in a transaction with verification:

BEGIN;
UPDATE prices SET amount = amount * 1.1 WHERE currency = 'USD';
-- Verify: SELECT COUNT(*), AVG(amount) FROM prices WHERE currency = 'USD';
COMMIT;  -- or ROLLBACK if something looks wrong

Batch updates for large tables:

-- Instead of updating 10 million rows at once, batch it
-- Loop until no rows remain (pseudo-code showing the pattern)
UPDATE orders
SET archived = TRUE
WHERE archived = FALSE
  AND created_at < '2023-01-01'
  AND order_id IN (
      SELECT order_id FROM orders
      WHERE archived = FALSE AND created_at < '2023-01-01'
      LIMIT 10000
  );
-- Repeat until ROW_COUNT is 0

Batching avoids long-running locks that block other queries.

Test on a narrow WHERE first:

-- Test on one row first
UPDATE customers SET tier = 'premium' WHERE customer_id = 1042;
-- Check: SELECT * FROM customers WHERE customer_id = 1042;
-- Then expand
UPDATE customers SET tier = 'premium'
WHERE total_spend > 10000 AND is_active = TRUE;