Update a Column Based on Conditions With CASE WHEN

Set a column to different values depending on other column values in the same row by using `CASE WHEN` inside a `SET` clause.

5/5 supported engines validation-green 1 example 2 scenarios

Docker-validated Not currently validation-green

Example 1

Assign a salary tier to every employee based on their pay

Alice (120000) matches the first condition (>= 100000) and is assigned senior. Dave (95000) and Bob (75000) both match the second condition (>= 60000) and become mid. Carol (45000) matches neither and falls through to the ELSE branch, receiving junior. All four rows are updated in one pass without needing four separate UPDATE statements.

Source table data Rows loaded before the example query runs.

Setup

CREATE TABLE employees (name VARCHAR(50), salary INT, tier VARCHAR(20));

INSERT INTO
  employees
VALUES
  ('Alice', 120000, NULL),
  ('Bob', 75000, NULL),
  ('Carol', 45000, NULL),
  ('Dave', 95000, NULL);

Validated query Shared across supported engines.

SQL

UPDATE employees
SET
  tier = CASE
    WHEN salary >= 100000 THEN 'senior'
    WHEN salary >= 60000 THEN 'mid'
    ELSE 'junior'
  END;

SELECT
  name,
  salary,
  tier
FROM
  employees
ORDER BY
  salary DESC;

Expected result Returned rows for the shared example.

name	salary	tier
Alice	120000	senior
Dave	95000	mid
Bob	75000	mid
Carol	45000	junior

The CASE WHEN syntax inside SET is identical across all supported engines.

Useful when

Where this command helps.

classifying employees into salary tiers based on their pay in a single pass
re-coding a status column from numeric codes to descriptive labels

Explanation

What the command is doing.

A CASE WHEN expression inside a SET clause lets a single UPDATE statement assign different values to rows that meet different conditions, replacing multiple separate updates. The expression evaluates each condition in order and uses the first matching THEN value; rows that match no condition fall through to the ELSE branch (or receive NULL if ELSE is omitted). This pattern is useful for categorising, re-coding statuses, and any situation where the new value depends on the existing data.

Update a Column Based on Conditions With CASE WHEN

Assign a salary tier to every employee based on their pay

Where this command helps.

What the command is doing.

Keep moving through nearby tasks.