x

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.

more ▼

asked Sep 10, 2010 at 06:28 AM in Default

sujafiza gravatar image

sujafiza
71 4 4 4

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

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
more ▼

answered Sep 10, 2010 at 07:33 AM

WilliamD gravatar image

WilliamD
25.9k 17 19 41

Thanks a lot..it really worked great..Will update you how it worked with huge volume of data.

Thanks, Su
Sep 10, 2010 at 08:14 AM sujafiza
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.
Sep 10, 2010 at 08:41 AM WilliamD
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."
Sep 10, 2010 at 09:30 AM sujafiza
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).
Sep 12, 2010 at 12:45 PM WilliamD
setting MAXRECURSION 0 will give you infinite recursion
Sep 12, 2010 at 08:00 PM Scot Hauder
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x129
x40
x21

asked: Sep 10, 2010 at 06:28 AM

Seen: 1099 times

Last Updated: Sep 13, 2010 at 02:47 AM