Abdullah Nurum Abdullah Nurum - 4 months ago 31
SQL Question

Update row number incorrect in mysql

this is My table in mysql

The image is mysql table structure. I want to update my rowNumber column based on AverageRate. If I Order by QuestionNumber which is primary key, it works perfect, but If I order by AverageRate It does not work. In MySql does not show anything wrong, but the result is not ranking by AverageRate. Where is proble? Thank you for helpers. Here is my code.

UPDATE Questioncopy t1
INNER JOIN (
SELECT @rowno := @rowno + 1 AS rowno, QuestionNumber
FROM Questioncopy
CROSS JOIN (SELECT @rowno := 0) t
ORDER BY AverageRate
) t2
ON t1.QuestionNumber = t2.QuestionNumber
SET t1.rowNumber = t2.rowno;


Here is image from the result Mysql table.
result of Mysql table

JPG JPG
Answer

Try this query and tell me the result please:

UPDATE Questioncopy t1
INNER JOIN (
    SELECT @rowno := @rowno + 1 AS rowno, QuestionNumber
    FROM (SELECT QuestionNumber FROM Questioncopy ORDER BY AverageRate) t1
    CROSS JOIN (SELECT @rowno := 0) t
) t2
ON t1.QuestionNumber = t2.QuestionNumber
SET t1.rowNumber = t2.rowno;