id	summary	reporter	owner	description	type	status	component	version	severity	resolution	keywords	cc	stage	has_patch	needs_docs	needs_tests	needs_better_patch	easy	ui_ux
20708	QuerySet.update() ignores order_by() clause	Jacob Rief	nobody	"Given a database model, say 'MyModel', with a unique integer field, say 'position'.

Attempt to shift all values of 'position' by a given positive offset:

ascending:
{{{MyModel.objects.order_by('position').update('position': F('position') - offset)}}}
or descending
{{{MyModel.objects.order_by('-position').update('position': F('position') + offset)}}}

The ORM ignores the order_by clause. The generated SQL is (offset = 1):
{{{UPDATE `myapp_mymodel` SET `position` = `myapp_mymodel`.`position` - 1;}}}

This is a problem with MySQL, because for bulk updates on a unique key, you must specify the working order, otherwise the above statement can cause an {{{IntegrityError: (1062, ""Duplicate entry..."")}}} on column 'position' since these values are updated out of order.

A solution would be to add ORDER BY position ASC / DESC (http://dev.mysql.com/doc/refman/5.1/en/update.html).
An alternative solution would be to ALTER TABLE ... DISABLE KEYS before updating and ALTER TABLE ... ENABLE KEYS after the update (http://dev.mysql.com/doc/refman/5.1/en/alter-table.html).

Postgres does not need/offer an ORDER BY clause on bulk updates.
"	Bug	closed	Database layer (models, ORM)	1.7	Normal	wontfix		slachinger	Unreviewed	0	0	0	0	0	0