0

I have the following code in MariaDB that was working perfectly well yesterday. It calculates a rank and orders by the data by another column:

SET @c = (SELECT COUNT(*) FROM myTable);
SET @rownum = 0;
UPDATE myTable
SET rank = (100 / @c) * (@rownum:= 1 + @rownum)
ORDER BY anotherColumn DESC LIMIT 100000;

Today I executed it and the first time through it gave the wrong ranking values. I don't think the SET @rownum = 0 had worked. Now it seems to be working fine again.

Does anyone know why it would work perfectly yesterday and then stop working today and behave so strangely?

I believe the @c is the variable that isn't updating when I add a new row. Does anyone know how to force it to recalculate @c based on the current data in the table?

I don't think the connection dropped out because I created the new row immediately after using HeidiSQL. I don't understand how it can even use an old value. If that was JavaScript, you would just get an error.

I'm using MariaDB version 5.2

1
  • 2
    Not the clearest clearest description however SET @c SET @rownum are session specific and not global so it might have dropped connection before the UPDATE was run. Note that this might be easier written as a window frame that has rank and row_number window functions. What wrong ranking values did you have?
    – danblack
    May 28, 2021 at 2:47

1 Answer 1

1

MariaDB 5.2 is really old. Upgrade.

MariaDB 10.2 (and MySQL 8.0) have RANK and other "windowing functions" to assist with your query.

Setting @variables inside SELECTs should not be trusted. Rewrite the query to avoid them.

Updating everyone's rank all the time will be a performance drag when you have a lot of users.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.