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/10d0d83b-733a-47d2-b5d1-6f9c904269a6%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
