Perform UPDATE and setting a user variable in one single query.

Question

Everyone familiar with php's mysql_query command, knows that it doesn't allow us to perform more than one query (separated by the ; delimiter) in one call...

My problem is that I wan't to be able to define a user variable in my UPDATE query so that I increment it for each row (kinda like auto increment). It should look like something as this:

SET @t1=0;
UPDATE `mytable` SET `order` = (@t1:=(@t1+1)) ORDER BY `order` ASC;

My problem is that, since I can't define my variable and then make the update, I can't find a way to set the variable inside the query. I've tried to define it if it was NULL:

... `order` = (IFNULL( @t1 := ( @t1 + 1 ) , @t1 := 0 )) ...

but it didn't worked since the variable resets at each row it works on.

Anyone familiar with mysql that see's a solution? Thanks in advance.

Answer 1

You could use the mysqli library, it allows for multiple querys in one query using the

mysqli->multiple_query( string $querys);

http://us.php.net/mysqli_multi_query

Answer 2

Going out on a limb, how about...

... `order` = (SELECT `order`+1 FROM `mytable` ORDER BY `order` DESC LIMIT 1)

or something like that as a subquery?... I'm not sure whether the subquery re-runs after each update, but if it does, it should select the previously highest order value and increment it?