April 8, 2014 at 4:57 pm
I'll try to explain the query. I already updated my previous post to add comments and remove an unnecesary column.
The first part is a recursive query that will add a row for each id change until it gets to the last possible option. Since we'll have more rows, we need to identify the order of them, so we add the counter column named as "n".
The second CTE (rownums) will get all the rows generated by the recursive CTE and will add a ROW_NUMBER that will go in the opposite way than n. That way we can ensure that the last value for each id will always have the same value on this column.
Finally, the update comes and updates the destination table with the last id using Id to join the table and the CTE and rn=1 to update with the last value for each Id.
It might seem complex and recursive CTEs seem like that at the beginning. Remember that in order to understand recursion, one must first understand recursion.
I hope you have a better idea of how the code works.
Viewing post 16 (of 16 total)
You must be logged in to reply to this topic. Login to reply