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

avatar image

sujafiza
72 4 4 8

(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

avatar image

WilliamD
26.2k 18 34 48

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.

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:

x162
x55
x22

asked: Sep 10, 2010 at 06:28 AM

Seen: 2223 times

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

Copyright 2016 Redgate Software. Privacy Policy