A common MySQL strategy to perform updates with accumulating
functions is to employ user-defined variables, using the
UPDATE [...] SET mycol = (@myvar := EXPRESSION(@myvar,
This pattern though doesn’t play well with the optimizer (leading to non-deterministic behavior), so it has been deprecated. This left a sort of void, since the (relatively) sophisticated logic is now harder to reproduce, at least with the same simplicity.
In this article, I’ll have a look at two ways to apply such logic: using, canonically, window functions, and, a bit more creatively, using recursive CTEs.[Read more]