How to make weights continous with db_update?

Question

With pure MySQL, I can do something like:

SET @weight:=0;
UPDATE mymodule_table
  SET mymodule_weight=@weight:=@weight+1
  ORDER BY mymodule_weight, mymodule_id;

Is there a Drupal way to achieve similar result? Can I avoid calls to db_query and push it with db_update or db_merge?

MySQL solution adapted from this answer.

Answer 1

Adding the expression is no problem, but neither UpdateQuery nor MergeQuery implement an orderBy() method. Neither will support setting a local variable first either, as far as I can tell (I'm not even sure a standard query will let you do that actually).

So I think it's safe to say that the answer is no, unless you subclass either UpdateQuery or MergeQuery, and add the sorting and variable declaration functionality yourself.