Скачать или смотреть How to Use SQL to Update Parent Records in a Hierarchy

Learn how to efficiently update parent-child hierarchical records in SQL with a recursive query. Discover a clear solution without the need for cursors!
---
This video is based on the question https://stackoverflow.com/q/64103109/ asked by the user 'Ryan H' ( https://stackoverflow.com/u/12447454/ ) and on the answer https://stackoverflow.com/a/64103246/ provided by the user 'Thom A' ( https://stackoverflow.com/u/2029983/ ) 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: Use SQL to update parent records in hierarchy

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 Use SQL to Update Parent Records in a Hierarchy

Updating records in a database can sometimes be a challenging task, especially when dealing with hierarchical structures. If you're working with a table that has a parent-child relationship, you might find yourself wondering how to update not just a specific record but also all of its parent records. In this guide, we will explore how to achieve this effectively using SQL.

Understanding the Problem

Let's say you have a table named Equipment that holds records in a hierarchical format. Each record may have a parent record, creating a structure that resembles a tree. The challenge is to update certain records based on their IDs and also ensure that all their parent records are updated along with them. For instance, if you want to update the ActiveInExecution field for the IDs 3, 5, and 6, it's essential to also update their parent records to reflect this change.

Here's a simplified view of how the Equipment table is structured:

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

In the example above, if you update IDs 3, 5, and 6, you must also update ID 2 (the parent of 3) and ID 4 (the parent of 5). Thus, the expected outcome is that the ActiveInExecution field for these IDs should be set to 1 (true).

The Solution: Recursive Common Table Expressions (CTE)

To tackle this scenario without using cursors—which can be cumbersome—we can utilize a Recursive Common Table Expression (CTE). This allows us to climb the hierarchy and gather all necessary records for the update in a straightforward manner.

Step-by-Step Implementation

Create the Equipment Table: First, ensure your Equipment table is set up correctly. Here's how you can create it:

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

Insert Sample Data: Here’s some data that you can insert to simulate your hierarchy:

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

Define the Recursive CTE: Next, set up your recursive CTE to fetch the IDs of the records you want to update as well as their parent records:

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

Perform the Update: Now you can update the ActiveInExecution field for all the relevant records identified by the CTE:

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

Verify the Results: Finally, you can check the results of your update with a simple SELECT statement:

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

Key Points to Note

Recursive CTE: This powerful feature allows you to traverse hierarchical data without complex loops or cursors, enabling efficient updates.

Efficiency: By using EXISTS within the UPDATE statement, you only target those records that are part of your CTE, ensuring no unnecessary updates occur.

Conclusion

Updating hierarchical records in SQL can be efficiently performed using Recursive Common Table Expressions. This not only helps keep your code clean and maintainable but also enhances its performance compared to traditional methods like cursors. By following the outlined steps, you can seamlessly update parent records in your database hierarchy, ensuring that all necessary relationships are honored.

For more advanced SQL techniques and tips, stay tuned and keep exploring the world of databases!