Batch Processing — Learn UPSERT, bulk updates, archiving, and deduplication in Practice

QUESTION 1

UPSERT — Combine inserts and updates in one query with INSERT ... ON CONFLICT DO UPDATE

INSERTON CONFLICTUPSERTAPI batch synchronization

Background ▼

UPSERT (Upsert = Update + Insert) is a pattern that performs “UPDATE if the record exists, INSERT otherwise” in one query. It is one of the most common patterns for synchronizing data from an external service in an API batch.

INSERT INTO target_table (col1, col2, ...)
VALUES (...)
ON CONFLICT (unique_col)          -- Specify the unique-key column used for conflict detection
DO UPDATE SET
  col2 = EXCLUDED.col2;          -- EXCLUDED = the value being inserted (the conflicting new data)

What is EXCLUDED?
It is a special table reference available inside ON CONFLICT DO UPDATE. It refers to the values of the row that was going to be inserted but conflicted. Use EXCLUDED.col for the new value and target_table.col for the existing value.

DO NOTHING means “skip the row when it conflicts.” It is useful when you need idempotency: running the same data repeatedly produces the same result.

Problem ▼

Synchronize a product master from an external EC service in a daily batch. In the products table, INSERT new products and UPDATE name/price/updated_at for existing products.

Synchronization data to write directly in VALUES: (external ID: EXT-001, name: Wireless Mouse Rev. 2, price: 3200), (EXT-002, Mechanical Keyboard, 8900), (EXT-003, 4-Port USB Hub, 1980)

Tables used

▸ products (before sync)

id	external_id	name	price	updated_at
1	EXT-001	Wireless Mouse	2800	2024-01-10
2	EXT-002	Mechanical Keyboard	8900	2024-01-10

Expected Output ▼

The products table after the query runs:

id	external_id	name	price	updated_at
1	EXT-001	Wireless Mouse Rev. 2	3200	2024-06-01
2	EXT-002	Mechanical Keyboard	8900	2024-06-01
3	EXT-003	4-Port USB Hub	1980	2024-06-01

※ EXT-001 updates its name, price, and updated_at; EXT-002 keeps its name and price but gets a new updated_at; EXT-003 is inserted as a new row.

Model Answer ▼

INSERT INTO products (external_id, name, price, updated_at)
VALUES
  ('EXT-001', 'Wireless Mouse Rev. 2', 3200, '2024-06-01'),
  ('EXT-002', 'Mechanical Keyboard', 8900, '2024-06-01'),
  ('EXT-003', '4-Port USB Hub', 1980, '2024-06-01')
ON CONFLICT (external_id)           -- Specify what to do when a unique constraint conflict occurs
DO UPDATE SET                       -- Update the following columns when a conflict occurs (UPSERT)
  name       = EXCLUDED.name,       -- EXCLUDED = the new data that was going to be inserted
  price      = EXCLUDED.price,
  updated_at = EXCLUDED.updated_at;

/*
  Logical execution order:
  1. VALUES        → Prepare the rows to insert
  2. INSERT        → Attempt to insert each row
  3. ON CONFLICT   → Detect duplicates
  4. DO UPDATE SET → Overwrite conflicting rows with the new values
*/

Explanation (table transitions & key points) ▼

INSERT INTO products (external_id, name, price, updated_at)
VALUES
  ('EXT-001', 'Wireless Mouse Rev. 2', 3200, '2024-06-01'),
  ('EXT-002', 'Mechanical Keyboard', 8900, '2024-06-01'),
  ('EXT-003', '4-Port USB Hub', 1980, '2024-06-01')
ON CONFLICT (external_id)
DO UPDATE SET
  name       = EXCLUDED.name,
  price      = EXCLUDED.price,
  updated_at = EXCLUDED.updated_at;

LEGEND ▼

Rows read / loaded

① Before synchronization ▼

products table (before sync)This is the products table before synchronization. id=1 (EXT-001) and id=2 (EXT-002) exist, and the external_id column has a UNIQUE constraint.

1 / 4

id	external_id	name	price	updated_at
1	EXT-001	Wireless Mouse	2800	2024-01-10
2	EXT-002	Mechanical Keyboard	8900	2024-01-10

2 existing rows

LEARNING POINTS

Basic refresher (how UPSERT works): The database basics are Create (INSERT), Read (SELECT), Update (UPDATE), and Delete (DELETE). Normally, INSERTing an existing ID raises an error, but ON CONFLICT avoids that error and switches to the safe behavior “UPDATE if it exists.”

The standard batch synchronization pattern: For importing data from an external API or CSV, UPSERT is safer than “DELETE everything, then INSERT everything.” Data never briefly disappears inside the transaction, and partial failures are easier to roll back.

Avoid unnecessary updated_at changes: In this query, updated_at is overwritten even when values such as EXT-002 are unchanged. Adding DO UPDATE SET ... WHERE EXCLUDED.price <> products.price updates only rows whose values actually changed, which is practical for logs and load.

ANTI-PATTERNS

Using ON CONFLICT without a UNIQUE constraint: The target columns of ON CONFLICT require a UNIQUE constraint or UNIQUE index. Otherwise PostgreSQL raises “ERROR: there is no unique or exclusion constraint.”

Including the primary key in DO UPDATE SET: Writing id = EXCLUDED.id attempts to overwrite an automatically assigned primary key and can cause an error. Do not include the PK or external_id (the conflict key) in SET.

PRACTICAL COLUMN

Making a batch idempotent
In production batch processing, rerunning a job after an error is routine. If it uses ordinary INSERT only, the retry fails because the ID already exists, forcing manual repairs. Making UPSERT the default option creates an operations-friendly batch whose repeated runs converge on the same correct state.

Model answers and explanations are for members

Read every model answer, explanation and table-transition visualization across all 48 advanced sets (240 questions)
One-time purchase — no subscription. Future advanced sets are included
Background, problem and expected output stay free

Unlock for $20 USD

Payments are securely processed by Stripe — your card details never touch this site.

You are charged in US dollars. If your card is issued in another currency, your bank’s exchange rate and any foreign transaction fee apply.

Already purchased? Sign in on your account page.

QUESTION 2

UPDATE FROM — Bulk-update multiple rows by reading another table

UPDATEFROMJOIN UPDATEBatch price revision

Background ▼

UPDATE ... FROM is a pattern for bulk-updating a target table while reading values from another table. It is central to batch processing, for example updating a product table from a price-revision master or changing statuses by matching an approved list.

UPDATE target                        -- Table to update
SET    col = src.col                -- Set a value from the source table
FROM   source src                   -- Source table (joined like a JOIN)
WHERE  target.key = src.key;       -- Join condition (without it, every row is updated!)

Forgetting the WHERE join condition updates every row: If you write FROM but omit its join condition in WHERE, every row in the target table is updated. Always add a WHERE condition on the join key.

PostgreSQL uses UPDATE ... FROM; MySQL uses UPDATE target JOIN source ON ....

Problem ▼

Use the price_updates (price revision master) table to bulk-update price and updated_at in products. Only products whose product_id exists in price_updates are targets.

Tables used

▸ products (before update)

product_id	name	price	updated_at
101	Coffee Bean A	1200	2024-01-01
102	Coffee Bean B	1500	2024-01-01
103	Black Tea Leaf C	900	2024-01-01
104	Green Tea D	800	2024-01-01

▸ price_updates (price revision master)

product_id	new_price	applied_at
101	1380	2024-06-01
103	1050	2024-06-01

Expected Output ▼

products after the update (only 101 and 103 found in price_updates change):

product_id	name	price	updated_at
101	Coffee Bean A	1380	2024-06-01
102	Coffee Bean B	1500	2024-01-01
103	Black Tea Leaf C	1050	2024-06-01
104	Green Tea D	800	2024-01-01

Model Answer ▼

UPDATE products
SET
  price      = pu.new_price,               -- Assign the new price from the source (pu)
  updated_at = pu.applied_at                -- Assign the source application date
FROM   price_updates pu                     -- Specify the source table (pu is an alias)
WHERE  products.product_id = pu.product_id; -- Join condition that selects target rows

/*
  Logical execution order:
  1. FROM  → Read the rows
  2. WHERE → Join and narrow the update targets
  3. SET   → Overwrite matching rows with new values
*/

Explanation (table transitions & key points) ▼

UPDATE products
SET
  price      = pu.new_price,
  updated_at = pu.applied_at
FROM   price_updates pu
WHERE  products.product_id = pu.product_id;

LEGEND ▼

Rows read / loaded

① Prepare two tables ▼

products ⋈ price_updatesPrepare the products target and the price_updates source. price_updates contains only product_id 101 and 103.

1 / 3

▸ products (target)

product_id	name	price	updated_at
101	Coffee Bean A	1200	2024-01-01
102	Coffee Bean B	1500	2024-01-01
103	Black Tea Leaf C	900	2024-01-01
104	Green Tea D	800	2024-01-01

⋈

▸ price_updates (source)

product_id	new_price	applied_at
101	1380	2024-06-01
103	1050	2024-06-01

Left: 4 rows / Right: 2 rows

LEARNING POINTS

Basic refresher (aliases and JOINs): Writing table_name AS alias (AS is optional) gives a long table name a shorter name in SQL. FROM price_updates pu is the classic example. Connecting separate tables with a shared key, here product_id, is called a JOIN.

Bulk-update performance: One UPDATE FROM query is much faster than looping over rows and issuing individual UPDATEs. N database round trips become one, greatly reducing round-trip cost.

UPDATE with a CTE: You can shape the source with a CTE and then update it, as in WITH updated AS (SELECT ...) UPDATE products SET ... FROM updated WHERE .... This is useful when the new values come from a complex aggregate.

ANTI-PATTERNS

Forgetting the FROM join condition in WHERE: UPDATE products SET price = pu.new_price FROM price_updates pu; — without WHERE, every product may be overwritten with a source value. Always put the join condition in WHERE.

Using the same syntax in MySQL: MySQL does not support UPDATE ... FROM. Write UPDATE products p JOIN price_updates pu ON p.product_id = pu.product_id SET p.price = pu.new_price instead.

PRACTICAL COLUMN

Long locks and chunking
Running UPDATE FROM on hundreds of thousands of records can hold a table lock long enough for a user-facing API to time out while waiting for a database connection. For large production updates, process a few thousand to a few tens of thousands of rows per chunk and add a short pause between chunks to reduce online impact.

Model answers and explanations are for members

Read every model answer, explanation and table-transition visualization across all 48 advanced sets (240 questions)
One-time purchase — no subscription. Future advanced sets are included
Background, problem and expected output stay free

Unlock for $20 USD

Payments are securely processed by Stripe — your card details never touch this site.

You are charged in US dollars. If your card is issued in another currency, your bank’s exchange rate and any foreign transaction fee apply.

Already purchased? Sign in on your account page.

QUESTION 3

INSERT INTO ... SELECT — Bulk-transfer and archive qualifying rows in another table

INSERT INTOSELECTArchivingBatch transfer

Background ▼

INSERT INTO ... SELECT inserts the result of a SELECT directly into another table. It is widely used in batch processing to move old data into an archive table or write aggregate results to a summary table.

INSERT INTO archive_table (col1, col2, ...)
SELECT       col1, col2, ...          -- SELECT column order and types must match the target
FROM         source_table
WHERE        condition;                -- Condition that narrows transfer targets

VALUES is unnecessary: INSERT INTO ... SELECT does not use a VALUES clause. The SELECT result itself becomes the rows to insert.

To delete rows from the source after archiving, pair DELETE + INSERT INTO...SELECT in one transaction. This prevents data from disappearing during the transfer.

Problem ▼

Bulk-copy completed orders from before 2024-02-01 (order_date < '2024-02-01') from orders into orders_archive. Set the archived_at column to the current time after copying.

Tables used

▸ orders

order_id	customer_id	amount	status	order_date
1001	C01	12000	completed	2024-01-15
1002	C02	8500	cancelled	2024-01-20
1003	C03	15000	completed	2024-01-28
1004	C01	9200	completed	2024-02-05
1005	C04	6700	completed	2024-02-10

▸ orders_archive (empty target)

order_id	customer_id	amount	status	order_date	archived_at
(empty)

Expected Output ▼

After execution, orders_archive receives two rows: 1001 and 1003.

order_id	customer_id	amount	status	order_date	archived_at
1001	C01	12000	completed	2024-01-15	2024-06-01 02:00:00
1003	C03	15000	completed	2024-01-28	2024-06-01 02:00:00

※ 1002 (cancelled), 1004, and 1005 (February or later) are excluded.

Model Answer ▼

BEGIN;

-- ① Select archive targets and insert them into orders_archive
INSERT INTO orders_archive (
  order_id, customer_id, amount, status, order_date, archived_at
)
SELECT
  order_id, customer_id, amount, status, order_date, NOW()
FROM  orders
WHERE order_date < '2024-02-01'
  AND status     = 'completed';

-- ② Delete from the source after the insert succeeds
DELETE FROM orders
WHERE order_date < '2024-02-01'  -- Keep exactly the same condition as the extraction
  AND status     = 'completed';

COMMIT;

/*
  Logical execution order:
  1. BEGIN         → Start the transaction
  2. FROM / WHERE  → Narrow the target rows
  3. INSERT        → Insert the extracted result into the archive
  4. DELETE        → Delete rows with the same condition from the source
  5. COMMIT        → Confirm the changes
*/

Explanation (table transitions & key points) ▼

BEGIN;
INSERT INTO orders_archive
  (order_id, customer_id, amount,
   status, order_date, archived_at)
SELECT
  order_id, customer_id, amount,
  status, order_date, NOW()
FROM  orders
WHERE order_date < '2024-02-01'
  AND status = 'completed';
DELETE FROM orders
WHERE order_date < '2024-02-01'
  AND status = 'completed';
COMMIT;

LEGEND ▼

Rows read / loaded

① FROM orders ▼

FROM orders — read all rowsRead all 5 rows from orders. The next step combines two WHERE conditions, date and status, to narrow the transfer targets.

1 / 4

order_id	customer_id	amount	status	order_date
1001	C01	12000	completed	2024-01-15
1002	C02	8500	cancelled	2024-01-20
1003	C03	15000	completed	2024-01-28
1004	C01	9200	completed	2024-02-05
1005	C04	6700	completed	2024-02-10

Read all 5 rows

LEARNING POINTS

Basic refresher (INSERT ... SELECT): Normally, new data is written as VALUES (value1, value2), but the result of a SELECT can be streamed directly into INSERT when moving many rows from another table. Column order and data types must match.

The practical archive-batch pattern: As a production table grows, its indexes grow and queries slow down. Periodically moving old data to an archive table or separate database keeps the production table lean.

CREATE TABLE AS SELECT (CTAS): CREATE TABLE new_tbl AS SELECT ... FROM ... creates a table and inserts data at the same time. It is often used to create temporary aggregate tables.

ANTI-PATTERNS

Using different WHERE conditions for INSERT and DELETE: A subtle difference, such as INSERT using “before January” while DELETE uses “through January” (<= '2024-01-31'), can leave data behind or delete too much. Copy the exact condition or define it once in a CTE.

Transferring a huge table all at once: Moving millions of rows in one transaction holds locks for a long time and can block the API. Split the work into pages with WHERE order_id BETWEEN ? AND ?.

PRACTICAL COLUMN

Modern operations with partitioning
In systems that generate tens of millions of rows each month, even batch DELETEs and transfers burden the database. A modern design can use PostgreSQL table partitioning, which physically separates data by month, then instantly remove an old partition with DROP TABLE or attach it directly to an archive database.

Model answers and explanations are for members

Read every model answer, explanation and table-transition visualization across all 48 advanced sets (240 questions)
One-time purchase — no subscription. Future advanced sets are included
Background, problem and expected output stay free

Unlock for $20 USD

Payments are securely processed by Stripe — your card details never touch this site.

You are charged in US dollars. If your card is issued in another currency, your bank’s exchange rate and any foreign transaction fee apply.

Already purchased? Sign in on your account page.

QUESTION 4

ROW_NUMBER deduplication — Extract only the latest record per key with a CTE

ROW_NUMBERCTEDeduplicationData cleansing

Background ▼

Duplicate keys can appear in a table after a batch import or a double submission. Combining ROW_NUMBER() with a CTE lets you write a cleansing query that keeps only the latest record within each duplicate-key group.

WITH ranked AS (
  SELECT *,
    ROW_NUMBER() OVER (
      PARTITION BY dup_key           -- Column used to identify duplicates
      ORDER BY     created_at DESC  -- Number rows newest first (1 is newest)
    ) AS rn
  FROM target_table
)
SELECT * FROM ranked WHERE rn = 1;  -- Return only rank 1 (latest) from each group

What is a CTE? (Common Table Expression)
WITH name AS (SELECT ...) defines a temporary named result set. The main query can reference it by name. It is more readable than nesting subqueries and is a standard way to write batch processing queries.

Problem ▼

The user_events table contains duplicate data because the same user_id and event_type combination was imported multiple times. Write a query that keeps only the latest record for each user_id + event_type.

Table used

▸ user_events (with duplicates)

event_id	user_id	event_type	payload	created_at
1	U01	login	ip:1.2.3.4	2024-06-01 09:00
2	U01	login	ip:5.6.7.8	2024-06-01 12:00
3	U01	purchase	item:A	2024-06-01 10:00
4	U02	login	ip:9.9.9.9	2024-06-01 11:00
5	U01	purchase	item:B	2024-06-01 15:00

Expected Output ▼

After deduplication (the latest row for each user_id + event_type):

event_id	user_id	event_type	payload	created_at	rn
2	U01	login	ip:5.6.7.8	2024-06-01 12:00	1
5	U01	purchase	item:B	2024-06-01 15:00	1
4	U02	login	ip:9.9.9.9	2024-06-01 11:00	1

Model Answer ▼

-- ① Define a temporary table (CTE) and number rows newest first within each group
WITH ranked AS (
  SELECT *,
    ROW_NUMBER() OVER (
      PARTITION BY user_id, event_type  -- Group by user_id and event_type
      ORDER BY     created_at DESC      -- Sort newest first within each group
    ) AS rn
  FROM user_events
)
-- ② Select only rn=1 (the latest row) from the CTE
SELECT
  event_id, user_id, event_type, payload, created_at, rn
FROM  ranked
WHERE rn = 1                            -- Exclude rn=2 and later (duplicates)
ORDER BY user_id, event_type;

/*
  Logical execution order:
  1. CTE definition (ROW_NUMBER) → Evaluate the window function (row count stays the same)
  2. FROM ranked / WHERE         → Filter the rows
  3. SELECT / ORDER BY            → Sort and output
*/

Explanation (table transitions & key points) ▼

WITH ranked AS (
  SELECT *,
    ROW_NUMBER() OVER (
      PARTITION BY user_id, event_type
      ORDER BY     created_at DESC
    ) AS rn
  FROM user_events
)
SELECT
  event_id, user_id, event_type,
  payload, created_at, rn
FROM  ranked
WHERE rn = 1
ORDER BY user_id, event_type;

LEGEND ▼

Rows read / loaded

① FROM user_events ▼

FROM user_events — read all rowsRead all 5 rows from user_events. U01/login and U01/purchase each have two duplicates. The next step groups them with PARTITION BY.

1 / 5

event_id	user_id	event_type	payload	created_at
1	U01	login	ip:1.2.3.4	2024-06-01 09:00
2	U01	login	ip:5.6.7.8	2024-06-01 12:00
3	U01	purchase	item:A	2024-06-01 10:00
4	U02	login	ip:9.9.9.9	2024-06-01 11:00
5	U01	purchase	item:B	2024-06-01 15:00

Read all 5 rows

LEARNING POINTS

Basic refresher (WITH / CTE): WITH name AS (SELECT ...) is a Common Table Expression (CTE), a way to split complex SQL into readable pieces. Think of it as creating a temporary view that the main SELECT can call by name.

Combine it with idempotent batch design: At design time, choose either deduplicate after ingestion or ingest with UPSERT so a second batch run does not leave duplicates. ROW_NUMBER deduplication is a safety net when UPSERT is unavailable.

Pattern for converting to DELETE: Safely confirm the rows to keep with SELECT, then convert the operation to DELETE. DELETE FROM user_events WHERE event_id NOT IN (SELECT event_id FROM ranked WHERE rn=1) has the same meaning.

ANTI-PATTERNS

Trying to solve it with DISTINCT: DISTINCT removes only rows whose entire set of columns is identical. It cannot handle a logical duplicate with a different event_id. ROW_NUMBER is the right tool.

Leaving multiple rows at the same timestamp with RANK: If two rows have exactly the same created_at, RANK or DENSE_RANK gives both rn=1. To guarantee one row, use ROW_NUMBER and add event_id or another tie-breaker to ORDER BY.

PRACTICAL COLUMN

At-Least-Once delivery in distributed systems
In event-driven architectures using message queues such as Kafka or SQS, network interruptions make it impossible to avoid receiving the same event multiple times under At-Least-Once delivery. It is therefore standard data-pipeline design to run a ROW_NUMBER deduplication batch in the cleansing layer after ingestion into a data lake or DWH, before analytics or downstream integrations.

Model answers and explanations are for members

Read every model answer, explanation and table-transition visualization across all 48 advanced sets (240 questions)
One-time purchase — no subscription. Future advanced sets are included
Background, problem and expected output stay free

Unlock for $20 USD

Payments are securely processed by Stripe — your card details never touch this site.

You are charged in US dollars. If your card is issued in another currency, your bank’s exchange rate and any foreign transaction fee apply.

Already purchased? Sign in on your account page.

QUESTION 5

CASE WHEN bulk status update — Set different values by condition in one UPDATE

UPDATECASE WHENStatus transitionsBatch state management

Background ▼

When a batch needs to UPDATE different values for several conditions, CASE WHEN in the SET clause combines the work into one query.

UPDATE orders
SET status =
  CASE
    WHEN condition A THEN 'value_a'   -- Rows matching condition A become value_a
    WHEN condition B THEN 'value_b'   -- Rows matching condition B become value_b
    ELSE              status       -- Rows matching neither condition keep their value
  END
WHERE filter condition;            -- Limit the UPDATE targets (important)

Why ELSE matters: If ELSE is omitted, rows matching no condition become NULL. When “leave unchanged” is intended, always write ELSE column_name (preserve the current value) or ELSE 'an appropriate default'.

Problem ▼

In a nightly batch, bulk-update statuses in the jobs table using these rules.

retry_count = 0 and status = 'failed' → 'pending' (requeue)
retry_count ≥ 3 and status = 'failed' → 'abandoned' (give up)
started_at is NULL and status = 'running' → 'stalled' (detect a zombie)
Leave all other rows unchanged

Table used

▸ jobs (before update)

job_id	status	retry_count	started_at
J01	failed	0	2024-06-01
J02	failed	3	2024-06-01
J03	running	0	NULL
J04	completed	0	2024-06-01
J05	failed	1	2024-06-01

Expected Output ▼

The jobs table after the update:

job_id	status (after update)	Change
J01	pending	failed(0) → pending
J02	abandoned	failed(3) → abandoned
J03	stalled	running(NULL) → stalled
J04	completed	No change
J05	failed	No change (retry_count=1)

Model Answer ▼

UPDATE jobs
SET status =
  CASE
    WHEN status = 'failed' AND retry_count = 0 THEN 'pending'   -- ① Requeue
    WHEN status = 'failed' AND retry_count >= 3 THEN 'abandoned' -- ② Give up
    WHEN status = 'running' AND started_at IS NULL THEN 'stalled' -- ③ Detect a zombie
    ELSE status                                                 -- ④ Preserve rows outside the conditions
  END
WHERE status IN ('failed', 'running');                          -- Narrow targets first

/*
  Logical execution order:
  1. WHERE → Narrow the update targets
  2. CASE  → Evaluate each row's value
  3. SET   → Overwrite the column with the selected value
*/

Explanation (table transitions & key points) ▼

UPDATE jobs
SET status =
  CASE
    WHEN status = 'failed' AND retry_count = 0
      THEN 'pending'
    WHEN status = 'failed' AND retry_count >= 3
      THEN 'abandoned'
    WHEN status = 'running' AND started_at IS NULL
      THEN 'stalled'
    ELSE status
  END
WHERE status IN ('failed', 'running');

LEGEND ▼

Rows read / loaded

① FROM jobs ▼

FROM jobs — scan all rowsRead all 5 rows from jobs. The next step first excludes statuses other than failed and running with WHERE IN, reducing the rows CASE WHEN must process.

1 / 4

job_id	status	retry_count	started_at
J01	failed	0	2024-06-01
J02	failed	3	2024-06-01
J03	running	0	NULL
J04	completed	0	2024-06-01
J05	failed	1	2024-06-01

All 5 rows

LEARNING POINTS

Basic refresher (IN and IS NULL): WHERE status IN ('A', 'B') means the same as status = 'A' OR status = 'B' and is a concise syntax. To test whether a value is NULL, SQL requires IS NULL, not = NULL; this is a fundamental SQL rule.

Implementing a batch state machine: Job queues and workflow engines often process status transitions in batches. CASE WHEN UPDATE is the standard implementation. Keeping the transition rules in one place makes the code readable.

Two layers of filtering with WHERE and CASE: Excluding non-target rows with WHERE before applying CASE reduces the number of rows the database scans. Completed rows are not processed at all.

ANTI-PATTERNS

Splitting each condition into a separate UPDATE: Three UPDATEs create three database round trips, and a row changed by the first UPDATE may match a later UPDATE condition. One CASE WHEN query is safer and faster.

PRACTICAL COLUMN

Save status-change history (audit log) at the same time
Production systems need to answer later when, which job, and why a job failed. Instead of only updating the status, insert the old status, new status, and reason into an audit table such as job_history at the same time. This makes operational troubleshooting much easier.

Model answers and explanations are for members

Read every model answer, explanation and table-transition visualization across all 48 advanced sets (240 questions)
One-time purchase — no subscription. Future advanced sets are included
Background, problem and expected output stay free

Unlock for $20 USD

Payments are securely processed by Stripe — your card details never touch this site.

You are charged in US dollars. If your card is issued in another currency, your bank’s exchange rate and any foreign transaction fee apply.

Already purchased? Sign in on your account page.