mysql update... order by

[Former user]

Some time, we need update a column with unique index. For example: "update tree set lft = lft + 2 where lft > 0". If the lft column is: "1, 2, 3, ..." MySQL will throw an duplication error. To solve the problem, a clause " order by lft DESC" need to be added.

But there is no order_by method in update() function in 0.5x.

[Mike Bayer]

i seriously doubt "UPDATE...ORDER BY" is standard SQL (although if it is, please let me know since that will allow it as a core feature). therefore just use a text() clause for this, or construct your own variant of update() using the recipe at http://www.sqlalchemy.org/docs/05/reference/ext/compiler.html .

I also wonder why this issue is not raised by common "nested sets" tutorials such as celko's ?

[Mike Bayer]

Replying to zzzeek:

i seriously doubt "UPDATE...ORDER BY" is standard SQL (although if it is, please let me know since that will allow it as a core feature).

MySQL docs seem to indicate its an extension. which would make sense because....

I also wonder why this issue is not raised by common "nested sets" tutorials such as celko's ?

I was a little unsure about this this morning but my memory with Oracle was always that constraints were processed for the UPDATE statement as a whole, and some additional reading seems to confirm this. so MySQL extends SQL in a clumsy way instead of fixing the issue.