[RESOLVED] mysql UPDATE query - can it be incremented between rows ?

steamPunk

imagine that i have an UPDATE like this :

"UPDATE table SET item_rank='".n."' WHERE cat_id='".$cat_id."'"

that will update all the rank column values to n

is it possible that the value n gets incremented with each row that is affected ?

the reason i'm asking is that at the moment i have do to a select to get a list of the id's of items in that category - then have a loop that updates each item in turn, then increment n and do the next one - this works fine but it's not very elegant

thanks

NogDog

If MySQL, I think you could do something like the following (untested):

$num = 1;
mysql_query('SET @num = ' . $num);
mysql_query("UPDATE table set item_rank = (SELECT @num := @num + 1) WHERE cat_id = '" . $cat_id . "'");

steamPunk

yessss !!!

that's perfect !

I had to initialize $num = 0; at the start otherwise it started numbering from 2 upwards - but apart from that it's great

many thanks

steamPunk

erm, i have another question :

is it possible to include something in that query that would update the rows in a specific order ?

the reason i'm asking this is that the items are already ordered by rank but if the rank order gets messed up (eg: 1,2,2,4,5,6,7 etc) I can detect that but i need to re-order the items using the existing order as a base

if it's not possible then i would need to do a SELECT anyway and order it by the existing order then do an update on each one

NogDog

You can add an ORDER BY clause at the end of the query (just like you would for a SELECT).

steamPunk

aha - as simple as that, eh ?!

I wouldn't have thought of doing that

thanks 🙂