Recursive update sql

I don't see here the need for recursion. But there is an other problem: what if a value to be changed is in the form of 3. If I add as parent, shows the above example. As you said, its possible to add as child like "From 2 to 2. What I mean is that if the row with the ID 5 contained 3. And what should happen when you insert a new child? Here ID is unique not changed.

If insert a row as parent "ChNum" column automatically increased. The same time, if delete any row, that time also "chNum" automatically rearranged. Add a comment. Active Oldest Votes. Improve this answer. MatheusOl MatheusOl 2, 15 15 silver badges 13 13 bronze badges. BTW, if someone dezso? Thanks lot MatheusOl One is: chNum order should be incremental order.

Not jump to 1. Is this possible? Second one is: is possible to move row between list. For e. I want keep primary key number. I'm Sorry MatheusOl for misunderstood. Second one, got it. Really nice Search online for the specific vendor's documentation if errors pop up. In mathematics, the Fibonacci numbers are the numbers in the following integer sequence, called the Fibonacci sequence, and characterized by the fact that every number after the first two is the sum of the two preceding ones:.

Here's some inline colorful comments to explain the sections:. Maybe arrows will help a little more with the flow of data:. Sorry, get a new computer or work with numbers less than digits long. Fibonacci sequence is nice and all, but you have real data concerns. So here's the data The parent of all the parents is Root. Of course, this is a simple implementation and many more columns would likely be needed for a production hierarchy.

But the simplicity of this table will suit our purposes for learning recursion. To make the data in this table match our diagram, we would load the table as follows:. Now that we have loaded the data we can code a query to walk the hierarchy using recursive SQL.

This is where the recursion comes in. A portion of the CTE definition refers to itself. Similar queries can be written to completely explode the hierarchy to retrieve all the descendants of any given node. Recursive SQL can be very elegant and efficient. What else are you going to do? You can create pre-exploded tables, but this requires denormalization and a lot of pre-processing which will not be efficient. Or you might write your own code to walk a hierarchy.

This, too, is fraught with potential problems. And how will you assure that your code is more efficient than the DBMS? The bottom line is that you should consider recursive SQL when business requirements call for it.