I encounter a similar problem. This issue is due to in MySQL "Currently,
you cannot update a table and select from the same table in a subquery.
<https://dev.mysql.com/doc/refman/5.0/en/update.html>" A workaround way (in
MySQL) is to create a template table from the selection:
UPDATE `djangoapp_mymodel` SET `foo_field` = 1 WHERE
`djangoapp_mymodel`.`id` IN (SELECT U0.`id` FROM `djangoapp_mymodel` U0 AS
template_table)
But I started to use django since yesterday and don't know how to fix it in
django. Any suggestions?
Thanks
Chi
On Thursday, May 14, 2015 at 2:26:04 PM UTC-4, Alejandro Treviño wrote:
>
> Hello everyone, first-time poster here!
>
> I ran into an interesting scenario earlier today that I thought was worth
> sharing:
>
> Given this update statement using the django ORM:
>
> >>> pks = MyModel.objects.all().values_list('pk', flat=True)
> >>> pks
> [1, 2, 3, 4, 5, 6, 7, 8, 9, 10, '...(remaining elements truncated)...']
> >>> MyModel.objects.filter(pk__in=pks).update(foo_field=True)
>
> When pks is a "reasonably small" size, the django ORM generates this
> (valid) SQL statement:
>
> UPDATE `djangoapp_mymodel` SET `foo_field` = 1 WHERE
> `djangoapp_mymodel`.`id` IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10)
>
> However, when pks is very large (300k records on my data set), it
> generates this instead:
>
> UPDATE `djangoapp_mymodel` SET `foo_field` = 1 WHERE
> `djangoapp_mymodel`.`id` IN (SELECT U0.`id` FROM `djangoapp_mymodel` U0)
>
> Which is not allowed in MySQL:
>
> django.db.utils.OperationalError: (1093, "You can't specify target
> table 'djangoapp_mymodel' for update in FROM clause")
>
>
> I'm wondering if this classifies as a bug, or if this is just a known
> limitation? My workaround is to just do the updates in smaller batches.
> An update on 100k records still generated valid SQL for me, but I haven't
> done enough testing to figure out what cut-off point is.
>
> *Environment:*
> Python 3.4.2
> Django 1.8.1
>
> mysql Ver 14.14 Distrib 5.5.43, for osx10.8 (i386) using readline 5.1
>
>
> Thanks!
>
> Alex
>
--
You received this message because you are subscribed to the Google Groups
"Django users" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to django-users+unsubscr...@googlegroups.com.
To post to this group, send email to django-users@googlegroups.com.
Visit this group at http://groups.google.com/group/django-users.
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-users/95bf9df8-5ea5-46de-87a4-36e4cc8171ce%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
