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.
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
