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