Single SQL updating multiple rows to unique values

Posted in Category • May 28, 2020

This is quick one, hope it is useful. TIL it is possible to update multiple database rows to unique values using a single SQL query. The trick is to use SQL CASE/WHEN for the updating value. This is very useful especially to implement re-ordering items of some sort, for example:

UPDATE "todos"
SET "sort_order" = CASE
  WHEN ("todos"."id" = 1) THEN 0
  WHEN ("todos"."id" = 157) THEN 1
  WHEN ("todos"."id" = 150) THEN 2
  WHEN ("todos"."id" = 158) THEN 3
  WHEN ("todos"."id" = 148) THEN 4
  ELSE "todos"."sort_order"
END
WHERE "todos"."id" IN (1, 157, 150, 158, 148);

Following is how you do it in Django:

from typing import Mapping

from django.db import models
from django.db.models import Case, When, F

class TodoQuerySet(models.QuerySet):
    def update_sort_order(self, orders: Mapping[int, int]):
        return self.update(
            sort_order=Case(*[
                When(pk=k, then=v) for k, v in orders.items()
            ], default=F('sort_order')
        )

    update_sort_order.queryset_only = True

class Todo(models.Model):
    ...
    user = models.ForeignKey('User', on_delete=models.CASCADE)
    sort_order = models.IntegerField(default=0)

    objects = TodoQuerySet.as_manager()

# Todo.objects.filter(user=request.user).update_sort_order({ 1: 10, 2: 3 })