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.
You could use the mysqli library, it allows for multiple querys in one query using the
mysqli->multiple_query( string $querys);
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?
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.