php - Reset a numeric sequence after deleting a row -

if have list of users following data:

+----+---------+----------+ | id | user    | sequence | +----+---------+----------+ |  2 | dave    |     1    | |  3 | sam     |     2    | |  4 | harry   |     3    | |  5 | sarah   |     4    | +----+---------+----------+ 

if delete user harry, re-order sequence column give me following:

+----+---------+----------+ | id | user    | sequence | +----+---------+----------+ |  2 | dave    |     1    | |  3 | sam     |     2    | |  5 | sarah   |     3    | +----+---------+----------+ 

i have tried following:

set @i = 0; update users set `sequence` = @i:=@i+1 order `sequence` asc; 

if run using mysql workbench works. when run via php method mysql_query failing error: you have error in sql syntax; check manual corresponds mysql server version right syntax use near.

it works if run statements separately. safe method - open problems? there workaround or better way write statement?

the application not using mysqli can't use http://php.net/manual/en/mysqli.multi-query.php

it fails because try multi_query. use perhaps http://php.net/manual/en/mysqli.quickstart.multiple-statement.php or try seperate it. in case put inside transaction, should safe. http://php.net/manual/en/mysqli.begin-transaction.php