Скачать или смотреть How to UPDATE a MySQL v5.5 Table with Row Numbers by Group

Learn how to easily update your MySQL v5.5 table by assigning row numbers within groups, even without the `ROW_NUMBER()` function.
---
This video is based on the question https://stackoverflow.com/q/64837806/ asked by the user 'Keith D Kaiser' ( https://stackoverflow.com/u/1861027/ ) and on the answer https://stackoverflow.com/a/64837994/ provided by the user 'Bill Karwin' ( https://stackoverflow.com/u/20860/ ) at 'Stack Overflow' website. Thanks to these great users and Stackexchange community for their contributions.

Visit these links for original content and any more details, such as alternate solutions, latest updates/developments on topic, comments, revision history etc. For example, the original title of the Question was: Is there a way to UPDATE a MySQL v5.5 table with row numbers by group?

Also, Content (except music) licensed under CC BY-SA https://meta.stackexchange.com/help/l...
The original Question post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license, and the original Answer post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license.

If anything seems off to you, please feel free to write me at vlogize [AT] gmail [DOT] com.
---
How to UPDATE a MySQL v5.5 Table with Row Numbers by Group

When working with databases, you might often want to assign a sequential row number to records within certain groups. In MySQL 8.0 and above, the ROW_NUMBER() function makes this process straightforward. However, if you're stuck using MySQL v5.5, there’s still a way to achieve this result using user-defined variables. In this guide, we’ll walk through the problem and provide a detailed solution to get you back on track.

The Problem: Row Numbers Without ROW_NUMBER()

If you're operating on a MySQL version 5.5 database, you may not have access to the ROW_NUMBER() function. A user encountered a challenge when trying to update a test table that stores records, assigning distinct row numbers to each entry grouped by netID. The structure of the table is as follows:

[[See Video to Reveal this Text or Code Snippet]]

The table contains records like this:

recordIDrow_numbernetIDvar1var2112388BillSmith212388TomSmith312388PatSmith...............The challenge was to update the row_number assigned to each record based on the netID grouping without the help of the built-in ROW_NUMBER() function.

The Solution: Using User-Defined Variables

While it might seem complex at first, there is actually a solution using MySQL’s user-defined variables. Here’s how you can update the table to assign the correct row numbers within groups.

Step-by-Step Instructions

Set Up User-Defined Variables: First, we need to initialize two variables. One will keep track of the current netID, and the other will maintain the current row_number.

[[See Video to Reveal this Text or Code Snippet]]

Update the Table: Next, we perform the UPDATE operation. In this step, we’ll make use of the CASE statement combined with user-defined variables to assign the correct values.

[[See Video to Reveal this Text or Code Snippet]]

Explanation of the Update Query

User-Defined Variables: The variables @ netID and @ row_number are used to keep track of the current group and the current row number respectively.

CASE Statement: Using the CASE statement, we check if the current row's netID matches the previous one. If it does, we increment the row number; if not, we reset and start again.

Order By Clause: Finally, the query needs to be ordered by both netID and recordID to ensure we process groups sequentially.

The Result

After executing the above update, your table would look like this:

recordIDrow_numbernetIDvar1var2112388BillSmith222388TomSmith332388PatSmith412390FredSmith522390JohnSmith632390HalJone712399DebJones822399KeithMackThis strategy utilizes a clever trick with subtraction to reset the row count for new netID groups.

Conclusion

Although MySQL v5.5 lacks modern features like ROW_NUMBER(), creative use of user-defined variables allows you to efficiently assign row numbers within groups. If possible, consider upgrading to a more recent version to simplify these tasks with window functions. In the meanwhile, with the method outlined above, you can continue managing your data effectively. Happy querying!