Updating a MySQL Table Based on Max docid per regid

Описание: Learn how to effectively update your MySQL table, setting the archived column to 1 based on the maximum docid value for each regid group.
---
This video is based on the question https://stackoverflow.com/q/71080455/ asked by the user 'Martin' ( https://stackoverflow.com/u/717192/ ) and on the answer https://stackoverflow.com/a/71081365/ 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: Updating table based on same table with a max value

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.
---
Updating a MySQL Table Based on Max docid per regid

Working with databases often leads to scenarios where you need to manipulate records based on certain conditions. One common challenge is updating specific rows in a database table according to the values in other rows. In this post, we tackle the problem of updating the archived column in a MySQL table based on the maximum docid for each regid group—an essential operation when managing grouped data efficiently.

Problem Overview

Imagine you have a table with a structure similar to the one below:

idregiddocidarchived110001021000203100030420001052000206300010730002083000309300040Your goal is to update the archived column to 1 for all rows where the docid is less than the maximum docid within each regid group. In this case, only the rows with docid 3, 5, and 9 should remain unchanged, while the others get updated.

The Challenge

You attempted to use a SQL query like this:

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

Unfortunately, this approach only worked for the first regid group and did not yield the desired results for the other groups.

Solution Steps

Using a Common Table Expression (CTE)

If you are using MySQL 8.0 or higher, you can easily solve this with a Common Table Expression (CTE). This method allows you to calculate row numbers based on the partition of regid while ordering by docid in descending order. Here’s how you can do that:

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

Explanation:

CTE Creation: The numbered_table CTE generates a temporary result set where each row is numbered based on the regid. The highest docid in each group receives a row number of 1.

Final Update: The main UPDATE statement then joins this temporary result set with the original table on id, setting archived to 1 for all rows with a rownum greater than 1.

Alternative for Older MySQL Versions

If you're using an older version of MySQL that doesn’t support CTEs, there's an alternative method that achieves the same result without needing to calculate the maximum docid explicitly:

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

Explanation:

Self-Join: This statement uses a self-join on the same table where t1 and t2 represent two views of the same table.

Condition: It checks for rows in t1 where there exists a corresponding row in t2 with a greater docid, forcing the update to target all but the maximum value within each regid.

Conclusion

Whether you're using the newer CTE syntax or an older self-join method, updating your MySQL table based on maximum values grouped by a specific column can be achieved effectively. By carefully structuring your SQL queries, you can manipulate your data in a way that both meets your needs and maintains the integrity of your records.

This guide should help you implement the necessary SQL updates with confidence. Happy querying!