Repository: django/django
Issue category: Feature Request

Support order_by().update() on MySQL/MariaDB.
Description
	 
When a UniqueConstraint is used on an IntegerField and we want to update multiple rows of that field at once, we need to make sure we select the right order before the update, since MySQL does not support a deferred update (see: ​https://dev.mysql.com/doc/refman/5.7/en/update.html).
I expected simply doing:
qs.order_by('-integerfield').update(integerfield=F('integerfield')+1) would work, but it seems the Django ORM strips the order_by from the query before execution, it gives a django.db.utils.IntegrityError: (1062, "Duplicate entry for key 'integerfield'").
Support order_by().update() on MySQL/MariaDB.
Description
	 
When a UniqueConstraint is used on an IntegerField and we want to update multiple rows of that field at once, we need to make sure we select the right order before the update, since MySQL does not support a deferred update (see: ​https://dev.mysql.com/doc/refman/5.7/en/update.html).
I expected simply doing:
qs.order_by('-integerfield').update(integerfield=F('integerfield')+1) would work, but it seems the Django ORM strips the order_by from the query before execution, it gives a django.db.utils.IntegrityError: (1062, "Duplicate entry for key 'integerfield'").


Functions to edit:
django/db/backends/mysql/compiler.py:SQLUpdateCompiler
Functions added: django/db/backends/mysql/compiler.py:SQLUpdateCompiler.as_sql

Patch:
diff --git a/django/db/backends/mysql/compiler.py b/django/db/backends/mysql/compiler.py
index 3682d6da05ef..1b4d583fb0c8 100644
--- a/django/db/backends/mysql/compiler.py
+++ b/django/db/backends/mysql/compiler.py
@@ -1,3 +1,4 @@
+from django.core.exceptions import FieldError
 from django.db.models.sql import compiler
 
 
@@ -36,7 +37,23 @@ def as_sql(self):
 
 
 class SQLUpdateCompiler(compiler.SQLUpdateCompiler, SQLCompiler):
-    pass
+    def as_sql(self):
+        update_query, update_params = super().as_sql()
+        # MySQL and MariaDB support UPDATE ... ORDER BY syntax.
+        if self.query.order_by:
+            order_by_sql = []
+            order_by_params = []
+            try:
+                for _, (sql, params, _) in self.get_order_by():
+                    order_by_sql.append(sql)
+                    order_by_params.extend(params)
+                update_query += ' ORDER BY ' + ', '.join(order_by_sql)
+                update_params += tuple(order_by_params)
+            except FieldError:
+                # Ignore ordering if it contains annotations, because they're
+                # removed in .update() and cannot be resolved.
+                pass
+        return update_query, update_params
 
 
 class SQLAggregateCompiler(compiler.SQLAggregateCompiler, SQLCompiler):
diff --git a/docs/ref/models/querysets.txt b/docs/ref/models/querysets.txt
index d70f7deca30e..6edc50866182 100644
--- a/docs/ref/models/querysets.txt
+++ b/docs/ref/models/querysets.txt
@@ -2530,6 +2530,21 @@ update a bunch of records for a model that has a custom
         e.comments_on = False
         e.save()
 
+Ordered queryset
+^^^^^^^^^^^^^^^^
+
+.. versionadded:: 3.2
+
+Chaining ``order_by()`` with ``update()`` is supported only on MariaDB and
+MySQL, and is ignored for different databases. This is useful for updating a
+unique field in the order that is specified without conflicts. For example::
+
+    Entry.objects.order_by('-number').update(number=F('number') + 1)
+
+.. note::
+
+    If the ``order_by()`` clause contains annotations, it will be ignored.
+
 ``delete()``
 ~~~~~~~~~~~~
 
diff --git a/docs/releases/3.2.txt b/docs/releases/3.2.txt
index 88c4cdc998e0..6c49cf537540 100644
--- a/docs/releases/3.2.txt
+++ b/docs/releases/3.2.txt
@@ -224,6 +224,9 @@ Models
 * The new :attr:`.UniqueConstraint.opclasses` attribute allows setting
   PostgreSQL operator classes.
 
+* The :meth:`.QuerySet.update` method now respects the ``order_by()`` clause on
+  MySQL and MariaDB.
+
 Requests and Responses
 ~~~~~~~~~~~~~~~~~~~~~~
 

Test patch: diff --git a/tests/update/models.py b/tests/update/models.py
index 786198760485..98f40a860302 100644
--- a/tests/update/models.py
+++ b/tests/update/models.py
@@ -41,3 +41,7 @@ class Foo(models.Model):
 class Bar(models.Model):
     foo = models.ForeignKey(Foo, models.CASCADE, to_field='target')
     m2m_foo = models.ManyToManyField(Foo, related_name='m2m_foo')
+
+
+class UniqueNumber(models.Model):
+    number = models.IntegerField(unique=True)
diff --git a/tests/update/tests.py b/tests/update/tests.py
index abf4db11d9d4..90844ccb6fc4 100644
--- a/tests/update/tests.py
+++ b/tests/update/tests.py
@@ -1,9 +1,12 @@
+import unittest
+
 from django.core.exceptions import FieldError
+from django.db import IntegrityError, connection, transaction
 from django.db.models import Count, F, Max
 from django.db.models.functions import Concat, Lower
 from django.test import TestCase
 
-from .models import A, B, Bar, D, DataPoint, Foo, RelatedPoint
+from .models import A, B, Bar, D, DataPoint, Foo, RelatedPoint, UniqueNumber
 
 
 class SimpleTest(TestCase):
@@ -199,3 +202,38 @@ def test_update_with_joined_field_annotation(self):
             with self.subTest(annotation=annotation):
                 with self.assertRaisesMessage(FieldError, msg):
                     RelatedPoint.objects.annotate(new_name=annotation).update(name=F('new_name'))
+
+
+@unittest.skipUnless(
+    connection.vendor == 'mysql',
+    'UPDATE...ORDER BY syntax is supported on MySQL/MariaDB',
+)
+class MySQLUpdateOrderByTest(TestCase):
+    """Update field with a unique constraint using an ordered queryset."""
+    @classmethod
+    def setUpTestData(cls):
+        UniqueNumber.objects.create(number=1)
+        UniqueNumber.objects.create(number=2)
+
+    def test_order_by_update_on_unique_constraint(self):
+        tests = [
+            ('-number', 'id'),
+            (F('number').desc(), 'id'),
+            (F('number') * -1, 'id'),
+        ]
+        for ordering in tests:
+            with self.subTest(ordering=ordering), transaction.atomic():
+                updated = UniqueNumber.objects.order_by(*ordering).update(
+                    number=F('number') + 1,
+                )
+                self.assertEqual(updated, 2)
+
+    def test_order_by_update_on_unique_constraint_annotation(self):
+        # Ordering by annotations is omitted because they cannot be resolved in
+        # .update().
+        with self.assertRaises(IntegrityError):
+            UniqueNumber.objects.annotate(
+                number_inverse=F('number').desc(),
+            ).order_by('number_inverse').update(
+                number=F('number') + 1,
+            )