UPDATE: Conditional and Multi Column Changes

Concept

UPDATE changes values in rows that already exist. It has exactly one dangerous property: the WHERE clause is optional. Leave it out and every row in the table is rewritten.

Syntax

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

Example

-- One column, one row
UPDATE employees
SET    salary = 95000.00
WHERE  id = 2;

-- Several columns at once, in one pass over the table
UPDATE employees
SET    salary  = salary * 1.10,
       status  = 'active'
WHERE  dept_id = 10;

-- Conditional logic: different rule per row, still one statement
UPDATE employees
SET    salary = CASE
                  WHEN hire_date < '2019-01-01' THEN salary * 1.12
                  WHEN hire_date < '2022-01-01' THEN salary * 1.08
                  ELSE salary * 1.05
                END
WHERE  status = 'active';

Explanation

The CASE version is worth studying. Written as three separate UPDATE statements it would work, but it would scan the table three times and the ranges would have to be mutually exclusive or raises would compound. One statement with CASE visits each row once and applies exactly one rule to it.

Note also salary = salary * 1.10: the right hand side always reads the value before this statement started, so there is no ordering surprise.

Updating from another table

-- MySQL / MariaDB: join in the UPDATE
UPDATE employees e
JOIN   departments d ON d.id = e.dept_id
SET    e.status = 'relocated'
WHERE  d.location = 'Pune';

-- PostgreSQL
UPDATE employees e
SET    status = 'relocated'
FROM   departments d
WHERE  d.id = e.dept_id AND d.location = 'Pune';

-- Portable everywhere: a correlated subquery
UPDATE employees
SET    status = 'relocated'
WHERE  dept_id IN (SELECT id FROM departments WHERE location = 'Pune');

Important rules

• No WHERE means every row. There is no confirmation prompt.
• All assignments in one SET happen together, reading the pre update values.
• An UPDATE that breaks a constraint fails and changes nothing.
• Updating a primary key that other tables reference triggers the foreign key rule - reject, cascade or set null.
• Updating an indexed column costs more than updating an unindexed one, because the index must be maintained too.

Common mistakes

• Running the statement with the WHERE clause still being typed. Every row changes and the old values are gone.
• Writing WHERE id = 1 OR id = 2 AND status = 'active' and hitting operator precedence.
• Using = in the SET clause for comparison. In SET, = always means assignment.
• Updating in a loop from application code when one set based statement would do the whole job.

Best practices

• Preview first. Run the same WHERE as a SELECT, confirm the row count, then convert it to an UPDATE.
• Wrap risky updates in a transaction so a wrong result can be rolled back:
  START TRANSACTION; UPDATE ...; SELECT ...; COMMIT;
• Turn on SQL_SAFE_UPDATES in MySQL so an update without a key is refused.
• Update by primary key wherever possible; it is both the safest and the fastest path.

Practice

1. Write the SELECT you would run before give every Sales employee a 7 percent raise, then the UPDATE.
2. Use a single statement with CASE to set order status to 'stale' for pending orders older than 90 days and 'review' for pending orders older than 30 days.
3. Explain why UPDATE employees SET salary = salary * 1.1; is a production incident and how a transaction would have saved you.