Issues

Issue #1452 new

mysql update... order by

Anonymous created an issue

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.

Comments (3)

  1. Mike Bayer repo owner
    • changed component to sql
    • changed milestone to 0.6.xx

    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 ?

  2. Mike Bayer repo owner

    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.

  3. Log in to comment