Tung on Cloud

Single SQL updating multiple rows to unique values

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 })