2

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.

AnthonyWJones
  • 187,081
  • 35
  • 232
  • 306

3 Answers3

3

Old question but here's an answer anyway:

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

IFNULL(@t1, 0) returns 0 if @t1 doesn't have a value or returns the value of @t1 if it has a value.

So on the first row @t1 is not set and it updates as order = (@t1 := 0 + 1) and on the following rows @t1 already has a value and adds +1 each row.

port-zero
  • 657
  • 3
  • 7
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

Pim Jager
  • 31,965
  • 17
  • 72
  • 98
0

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?

iopener
  • 547
  • 1
  • 9
  • 24