Opened 7 years ago

Last modified 7 years ago

#28787 closed Bug

QuerySet.update() fails on MySQL if a subquery references the base table — at Version 2

Reported by: Paulo Owned by: nobody
Component: Database layer (models, ORM) Version: 1.11
Severity: Normal Keywords:
Cc: Mariusz Felisiak Triage Stage: Accepted
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description (last modified by Tim Graham)

Given the models below:

class House(models.Model):
    name = models.CharField(max_length=200)

class Room(models.Model):
    name = models.CharField(max_length=200)
    house = models.ForeignKey(House, on_delete=models.CASCADE)

The following query fails:

houses_with_suites = House.objects.filter(room__name__icontains='suite')
Room.objects.filter(house__in=houses_with_suites).update(name='suite')

With this error:

OperationalError: (1093, "You can't specify target table 'mysql_test_room' for update in FROM clause")

I think this has been around for a while (#20300).

I've confirmed this only happens in MySQL. It works on PostgresSQL and SQLite.

Change History (2)

comment:1 by Paulo, 7 years ago

Description: modified (diff)

comment:2 by Tim Graham, 7 years ago

Component: UncategorizedDatabase layer (models, ORM)
Description: modified (diff)
Summary: MySQL Update failsQuerySet.update() fails on MySQL if a subquery references the base table
Triage Stage: UnreviewedAccepted
Type: UncategorizedBug
Note: See TracTickets for help on using tickets.
Back to Top