AlgoInsight

Q&A

How to use the UPDATE statement in SQL

By AlgoInsight Team2026-06-07sqlsql

Overview

UPDATE is one of the four core data-manipulation operations (alongside SELECT, INSERT, DELETE). It changes the values of one or more columns in rows that match a condition. The single most important habit with UPDATE is always pairing it with a precise WHERE clause - a missing or wrong WHERE can silently corrupt an entire table.

graph TD
    A["UPDATE table"] --> B["SET col1 = v1, col2 = v2"]
    B --> C{WHERE condition?}
    C -->|yes| D[Only matching rows updated]
    C -->|NO WHERE| E[⚠ ALL rows updated!]
    style E fill:#6366f1,color:#fff
    style D fill:#1e1b4b,color:#fff

Basic Syntax

UPDATE employees
SET salary = 75000
WHERE id = 101;

This changes salary to 75000 for the single employee with id = 101.

Updating Multiple Columns

UPDATE employees
SET salary = 80000,
    title  = 'Senior Engineer',
    updated_at = NOW()
WHERE id = 101;

Using Expressions

The new value can reference the current value or other columns:

UPDATE products
SET price = price * 1.10          -- 10% increase
WHERE category = 'electronics';

Conditional / Bulk Updates

UPDATE orders
SET status = 'shipped'
WHERE status = 'paid'
  AND created_at < '2026-01-01';

Updating Based on Another Table

With a subquery

UPDATE employees
SET department_name = (
    SELECT name FROM departments
    WHERE departments.id = employees.dept_id
);

With a JOIN (MySQL syntax)

UPDATE employees e
JOIN departments d ON e.dept_id = d.id
SET e.department_name = d.name
WHERE d.active = 1;

With FROM (PostgreSQL syntax)

UPDATE employees e
SET department_name = d.name
FROM departments d
WHERE e.dept_id = d.id;

(JOIN-in-UPDATE syntax varies by database - MySQL, PostgreSQL, and SQL Server each differ.)

Conditional Values with CASE

UPDATE employees
SET bonus = CASE
    WHEN performance = 'A' THEN salary * 0.15
    WHEN performance = 'B' THEN salary * 0.10
    ELSE 0
END;

Safety Practices

  1. Always write the WHERE first, or test with a SELECT using the same condition:
    SELECT * FROM employees WHERE id = 101;   -- verify target rows
  2. Use transactions so you can roll back a mistake:
    BEGIN;
UPDATE employees SET salary = 80000 WHERE id = 101;
-- check the result, then:
COMMIT;   -- or ROLLBACK;
  3. Some clients/databases support safe-update mode that blocks UPDATE without a key-based WHERE.
  4. Back up before large bulk updates.

Why This Matters in Interviews

SQL interviews test whether you understand that UPDATE without WHERE hits every row (a famous production disaster), how to update from related tables (subquery vs JOIN), expression-based updates, and CASE for conditional logic. Mentioning transactions and "SELECT-before-UPDATE" verification signals operational maturity.

Common Pitfalls

References

More answers