So I have this query
query = "SET @rownumber = 0; UPDATE students SET roll_no = (@rownumber := @rownumber+1) WHERE standard='{}' AND division='{}' ORDER BY fname, lname".format(standard, division)
When I try to run this query using cursor.execute(query)
, I get this following error using
pymysql.err.ProgrammingError: (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'UPDATE students SET roll_no = (@rownumber := @rownumber+1) WHERE standard='4' AN' at line 1")
But this same query works fine when I run it on MYSQL workbench ? Why is it not working and how can I get it to work ?
UPDATE students
SET roll_no = (@rownumber := @rownumber+1)
WHERE standard='{}'
AND division='{}'
AND (1 + (@rownumber := 0))
ORDER BY fname, lname
What happens?
Execution plan builder finds constant condition AND (1 + (@rownumber := 0))
(it does not include any column) and evaluates it once before query execution for to convert it to a constant. The value obtained is 1 which is treated as TRUE constant, so this condition is removed. But the user-defined variable initializes during evaluation.
This method allows both initialize the variable and store ORDER BY usage, because it do not convert the query to multi-table form (like when initialize the variable in subquery) in which ORDER BY clause is not allowed.