question

sujafiza avatar image
sujafiza asked

Updating a value from initial version to all its higher version with out looping.

Hello, I have a table Document where the versioning of each document is managed in a linked list format, CREATE TABLE Document ( DocumentID NUMERIC(16,0), OriginatingID NUMERIC (16,0), PrevVesrionID NUMERIC(16,0), NextVersionID Numeric(16,0)) INSERT INTO Document SELECT 100,100,0,110 UNION ALL SELECT 110,0,100,120 UNION ALL SELECT 120,0,110,130 UNION ALL SELECT 130,0,120,0 UNION ALL SELECT 200,200,0,210 UNION ALL SELECT 210,0,200,220 UNION ALL SELECT 220,0,210,0 My requirement is to update the OriginatingID(same as the initial version DocumentID) of the first version to all its higher versions by a simple UPDATE statement with out giving much hit to performance as there are millions of records present in the table. I have a function fnGetHigherVersionIDs that returns me a table with all the higher version documentID's for a given initial versionID. The function logic is as follows. CREATE FUNCTION fnGetHigherVersionIDs (@DocumentID NUMERIC(16,0)) RETURNS @tbl TABLE (DocumentID NUMERIC(16,0)) AS BEGIN ;WITH CTE(DocumentID) AS ( SELECT DocumentID FROM dbo.Document (NOLOCK) WHERE PreviousVersionID = @DocumentID UNION ALL SELECT d.DocumentID FROM dbo.Document d (NOLOCK) INNER JOIN CTE ON CTE.DocumentID = d.PreviousVersionID ) INSERT INTO @tbl SELECT DocumentID FROM CTE RETURN END Thanks for all your help in advance, Su.
updatecterecursion
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
WilliamD avatar image
WilliamD answered
Something like this should do the trick (recursive CTE to build a document family tree and then update the base table using this data): ; WITH FamilyTree(DocumentID, ParentDocumentId, ChildDocumentId)/* Recursive CTE to generate the ParentKey information that is missing from the imported data. */ AS (/* First part finds the parent documents (anything with no parent itself) and generates the anchor data-set for the recursion */ SELECT DocumentID, DocumentID, NextVersionId FROM dbo.Document WHERE PreviousVersionID = 0 UNION ALL /* Second part now joins all documents from the base table with the anchor data to find the children. Note that the ParentDocumentId is basically a constant (the anchor parent)*/ SELECT children.DocumentID, parents.ParentDocumentId, children.NextVersionId FROM dbo.Document children INNER JOIN FamilyTree parents ON parents.ChildDocumentId = children.DocumentId ) /* Now join the Document Family Tree to the original Document table and set the OriginatingId for all entries that are currently not set*/ UPDATE d SET OriginatingId = FamilyTree.ParentDocumentId FROM FamilyTree INNER JOIN dbo.Document d ON d.DocumentId = FamilyTree.DocumentID WHERE OriginatingId=0
6 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

setting MAXRECURSION 0 will give you infinite recursion
1 Like 1 ·
Thanks a lot..it really worked great..Will update you how it worked with huge volume of data. Thanks, Su
0 Likes 0 ·
If it is to be run on a large amount of data, you may want to batch the updates into a smaller subset of the data.
0 Likes 0 ·
Oh yes..I was stopped by "Msg 530, Level 16, State 1, Line 2 The statement terminated. The maximum recursion 100 has been exhausted before statement completion."
0 Likes 0 ·
A really weak answer to this is to supply the MAXRECURSION option and override the default of 100 with something a little higher (max is a squeeze above 32000).
0 Likes 0 ·
Show more comments

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.