Right, it has to be done on the same connection, but it can be done in 2 consecutive queries. If the initial set to 0 would not have worked, the increment would have worked either. Selecting an undefined user variable results in NULL. Adding one to that not initialzed variable leaves its value at NULL.
So if it worked right away both statements must have worked 
...schrieb am 26. 03. 2009 um 10:29 Uhr
Renumber an ordered list in a MySQL table
Did you ever have a table containing evenly numbered entries and had to delete an entry in the middle? How to renumber the remaining entries? Here's a trick for MySQL using a user variable to recount the index:
set @num=0;
update TABLE set IDX=@num:=@num+1 where FILTERCOL='only these' order by IDX;
This statement renumbers all entries in table TABLE which fulfill the filter condition FILTERCOL='only these' by using a user variable @num which is auto-incremented after assigning to the IDX field. The solution is fast, elegent ... and not portable
Important, use it only at the same connection. Trying execute this two queries by PHP - it will not work. Because PHP can only execute a single query in fact to protect for SQL-injection. So the first one set @num=0; becames no sense why at the 2nd query @num will not be exists (new connection). Maybe a stored procedure could be the solution?
...schrieb am 09. 02. 2009 um 14:53 Uhr