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

Question

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.

Answer 1

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

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

Follow this question

Related Questions