Using Annotation As Update Parameter Generates Invalid SQL

[David Sanders]

If you try to use an annotation which spans tables in an update, everything will resolve and attempt to execute but the SQL will fail because for MySQL the query is broken into a SELECT and then an UPDATE, but the necessary info is not available in the UPDATE. On sqlite3 it generates an UPDATE that looks similar but with the SELECT as a subquery.

Example:

class Bar(models.Model):
    name = models.CharField(max_length=32)

class Foo(models.Model):
    related_bar = models.ForeignKey(Bar)
    bar_name = models.CharField(max_length=32)

Foo.objects.annotate(bar_name=F('related_bar__name')).update(name=F('bar_name'))

On MySQL produces:

SELECT `test_foo`.`id`, `test_bar`.`name` AS `bar_name` FROM `test_foo` LEFT OUTER JOIN `test_bar` ON ( `test_foo`.`related_bar` = `test_bar`.`id` );

UPDATE `test_foo` SET `bar_name` = `test_bar`.`name` WHERE `test_foo`.`id` IN (1);

I don't think there's a trivial fix for this. Doing an UPDATE with a JOIN appears to have different syntax in the various implementations and isn't possible at all on sqlite3, so it would need to be multiple queries.

Seems that until it can be made to work correctly an error should occur if attempted, similar to the ​error for aggregates in an UPDATE.