skip to content

In Django, how does update() with Case/When differ from bulk_update() and a save() loop when assigning loyalty tiers from each customer's points?

level: middleimportance: should knowfreq 37%

answer

  1. where is the tier computed
  2. one statement versus one per batch
  3. bulk_update builds CASE per primary key
  4. first matching When wins
  5. forget default and get NULL

basics

~20 s

update(tier=Case(When(...), default=...)) computes every tier inside one UPDATE from the stored points; bulk_update() needs the instances loaded and sends Python-computed values as CASE WHEN pk=... per batch; a save() loop sends one UPDATE per customer but runs save() and signals.

solid answer

~40 s

With `Customer.objects.update(tier=Case(When(loyalty_points__gte=5000, then=Value("gold")), When(loyalty_points__gte=1000, then=Value("silver")), default=Value("bronze")))` the database evaluates the rule per row in a single `UPDATE`; nothing is loaded into Python. `bulk_update(objs, ["tier"])` is for values you computed in Python: Django itself builds a `CASE WHEN pk = ... THEN ...` per object and runs one `UPDATE ... WHERE pk IN (...)` per batch, so you pay for loading the rows, a large SQL string and values that may be stale by the time they are written. A `save()` loop is one `UPDATE` per customer but is the only one that runs `save()` and `pre_save`/`post_save`. Two traps with `Case`: `When` clauses are checked in order, so put the highest threshold first, and without `default` unmatched rows get NULL.

code

python · 13 lines
python
from django.db.models import Case, Value, When

from shop.models import Customer


def reassign_tiers():
    return Customer.objects.update(
        tier=Case(
            When(loyalty_points__gte=5000, then=Value("gold")),
            When(loyalty_points__gte=1000, then=Value("silver")),
            default=Value("bronze"),
        )
    )

go deeper

for a junior

Recall that Case and When let one update() assign different values to different rows based on a condition.

for a middle

Explain first-match ordering, the NULL default, and how bulk_update() differs by needing loaded instances and generating CASE per primary key.

for a senior

Choose per job: SQL-expressible rules in one update, Python-computed values in batched bulk_update, hooks only where required, with staleness and lock time in mind.

for a principal

Set expectations for batch jobs, for example that nightly recomputes never loop save() over whole tables without a written reason.

## The task Tiers depend on points: 5,000 or more is gold, 1,000 or more is silver, everything else is bronze. After a points recompute, every customer's `tier` must be reassigned. There are three Django ways to write it. ## 1. `update()` with `Case`/`When`: the rule runs in SQL `Case` and `When` are **conditional expressions**: Django compiles them to SQL `CASE WHEN ... THEN ... ELSE ... END`. ```python Customer.objects.update( tier=Case( When(loyalty_points__gte=5000, then=Value("gold")), When(loyalty_points__gte=1000, then=Value("silver")), default=Value("bronze"), ) ) ``` - One `UPDATE` for all rows; the database reads each row's `loyalty_points` and picks the tier. - `When` takes the same lookups as `filter()` (or a `Q` object) plus `then=`. - **Order matters**: SQL `CASE` returns the first matching branch, so the gold check must come before silver; reversed, every gold customer becomes silver. - **`default` matters**: without it Django uses `None`, so unmatched rows get NULL, which fails a `NOT NULL` column with an integrity error or silently stores NULL in a nullable one. - The usual `update()` caveats apply: no `save()`, no signals, no `auto_now`. ## 2. `bulk_update()`: values computed in Python `bulk_update(objs, fields, batch_size=None)` writes values **already set on instances**. Internally it builds, for each field, a `Case` with one `When(pk=obj.pk, then=value)` per object, and runs `filter(pk__in=pks).update(...)` once per batch. - You must **load the instances first**, then compute tiers in Python. - The SQL grows with the number of objects; the docs recommend a `batch_size` for many rows and columns, and warn that all `WHEN` clauses are prepared before the first query runs, which costs memory. - The values are **snapshots**: if points changed between loading and writing, you write a tier for the old points. - Like `update()`, it skips `save()` and `pre_save`/`post_save`, and it cannot change the primary key. It is the right tool when the new value genuinely needs Python: a call to a pricing service, a rule too complex for SQL, data from a file. ## 3. The `save()` loop: model behaviour, one row at a time ```python for customer in Customer.objects.all(): customer.tier = compute_tier(customer.loyalty_points) customer.save(update_fields=["tier"]) ``` - One `UPDATE` per customer after one `SELECT`. - The only option that runs an overridden `save()`, the signals and `auto_now`. - Acceptable for a few hundred rows or when those hooks are required; wrong for a nightly job over the whole table. ## Side by side | | `update()` + `Case` | `bulk_update()` | `save()` loop | |---|---|---|---| | Where the tier is computed | Database | Python | Python | | Rows loaded into Python | None | All | All | | Statements | 1 | 1 per batch | 1 per row | | Uses current points at write time | Yes | No, the loaded snapshot | No, the loaded snapshot | | Runs `save()` / signals | No | No | Yes | ## Narrowing the write An unconditional `update()` rewrites every row even when most tiers do not change, which costs write volume and holds locks on rows that did not need touching. Two refinements keep it cheap: - **Filter to rows that change.** Run one `update()` per band with a filter that excludes customers already in it, for example `Customer.objects.filter(loyalty_points__gte=5000).exclude(tier="gold").update(tier="gold")`; three small statements can beat one statement over the whole table. - **Read the return value.** `update()` returns the number of rows matched, which is a cheap sanity check in a management command's output. The `Case` expression can also be used in `annotate()` to preview the result before writing: `Customer.objects.annotate(new_tier=Case(...)).exclude(tier=F("new_tier")).count()` tells you how many rows the update would change. ## Choosing 1. Rule expressible over the row's own columns → `update()` with `Case`/`When`. 2. Value needs Python → `bulk_update()` with a `batch_size`, loading in chunks. 3. Per-object hooks required → the `save()` loop with `update_fields`, or `update()` plus an explicit follow-up for the hooks. Filters can narrow any of them: updating only rows whose tier would change (`.exclude(tier=...)` per band, or a `filter()` on the condition) cuts write volume further.

  • What goes wrong if the When clauses are written silver first, then gold?
    SQL `CASE` stops at the first true condition. A customer with 6,000 points satisfies `loyalty_points__gte=1000` first, so they are labelled silver and the gold branch is never reached. Order `When` clauses from the most specific to the least, or make the conditions mutually exclusive with ranges.
  • Why can bulk_update() write the wrong tier even though its SQL is correct?
    It writes values computed from instances loaded earlier. If a purchase changes a customer's points between the load and the `bulk_update()` call, the tier reflects the old points. `update()` with `Case` evaluates the rule against the value in the row at write time, so it cannot go stale that way.

update() with Case is posting the tier rules on the warehouse wall so every shelf labels itself; bulk_update() is writing each label at your desk and carrying the stack back; the save() loop is walking to each shelf with one label at a time.

saying these in an interview costs you the question

  • Case without default leaves unmatched rows unchanged
  • bulk_update() sends post_save for every object it writes
  • When clauses are all evaluated and the last match wins
  • bulk_update() computes new values inside the database
  • A save() loop with update_fields is as cheap as one UPDATE