Skip to content

support ORDER BY in db.update #31

Closed
anandology opened this Issue Nov 28, 2010 · 1 comment

1 participant

@anandology

Bug reported in Launchpad.

https://bugs.launchpad.net/webpy/+bug/598080

Although UPDATE ... SET ... ORDER BY .... is valid SQL, current db module doesn't support this. Current documentation shows arguments for db.select works for db.update, and there's no mention about order is not working in update.

Supposed to be work:

ret = self.db.update('Articles', vars = val, 
    where = 'bSerial = $board_id AND aIndex >= $index', 
    order = 'aIndex DESC', 
    aIndex = web.SQLLiteral('aIndex + 1'))

Result:

ERR: UPDATE Articles SET order = 'aIndex DESC', aIndex = aIndex + 1 
    WHERE bSerial = 1371L AND aIndex >= 48L

Finally:

ProgrammingError: (1064, "You have an error in your SQL syntax; 
    check the manual that corresponds to your MySQL server version for 
    the right syntax to use near 'order = 'aIndex DESC', aIndex = aIndex + 1 
    WHERE bSerial = 1371 AND aIndex >= 48' at line 1")
@anandology

Looks like UPDATE order by is supported only by MySQL. It fails in Postgres.

webpy=# update person set name='x' order by id limit 1;
ERROR:  syntax error at or near "order"
LINE 1: update person set name='x' order by id limit 1;
                               ^

I don't think it is worth adding support for this.

@anandology anandology closed this Jun 21, 2011
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Something went wrong with that request. Please try again.