Hi,

I've millions of records something like this in the SQL Server 2000 database.

ID Version
--- -------
1 1
1 NULL
1 NULL
2 1
2 NULL
2 NULL

Please provide me an simple update query which update the NULL with the incremental value of version by grouping the ID and my updation should not affect my performance and keep in mind that i have millions of records.

My final output after updation:

ID Version
--- -------
1 1
1 2
1 3
2 1
2 2
2 3




Thanks,
Chandra

__________________
Thanks,
Chandra

Here is a solution for you with no tampering with original table.


DECLARE @Sample TABLE (ID INT, Version INT)

INSERT @Sample
SELECT 1, 1 UNION ALL
SELECT 1, NULL UNION ALL
SELECT 1, NULL UNION ALL
SELECT 4, 1 UNION ALL
SELECT 4, 3 UNION ALL
SELECT 4, NULL

SELECT *
FROM @Sample

DECLARE @MinVersion INT,
    @MaxVersion INT,
    @MinID INT,
    @MaxID INT

SELECT @MinVersion = 1,
    @MaxVersion = MAX(Version),
    @MinID = MIN(ID),
    @MaxID = MAX(ID)
FROM @Sample

SET ROWCOUNT 1

WHILE @MinVersion <= @MaxVersion
    BEGIN
        WHILE @MinID <= @MaxID
            BEGIN
                IF NOT EXISTS (SELECT * FROM @Sample WHERE ID = @MinID AND Version = @MinVersion)
                    UPDATE @Sample
                    SET Version = @MinVersion
                    WHERE ID = @MinID
                        AND Version IS NULL

                SELECT @MinID = MIN(ID)
                FROM @Sample
                WHERE ID > @MinID
            END

        SELECT @MinVersion = @MinVersion + 1,
            @MinID = MIN(ID)
        FROM @Sample
    END

SET ROWCOUNT 0

SELECT *
FROM @Sample