BEGIN TRAN
DECLARE @CONTACT_ID VARCHAR(15)
DECLARE @TYPE VARCHAR(15)
DECLARE @INDEX_NO SMALLINT
DECLARE @COUNTER SMALLINT
DECLARE @FETCH_STATUS INT
DECLARE OUTER_CURSOR CURSOR
FOR
SELECT CONTACT_ID, TYPE, INDEX_NO FROM CONTACTS
WHERE
CONTACT_ID IN (SELECT CONTACT_ID FROM dbo.CONTACTS
WHERE CONTACT_ID IN(...)
GROUP BY CONTACT_ID, TYPE, INDEX_NO
HAVING COUNT(*) > 1
OPEN OUTER_CURSOR
FETCH NEXT FROM OUTER_CURSOR INTO @CONTACT_ID, @TYPE, @INDEX_NO
WHILE (@@FETCH_STATUS <> -1)
BEGIN
IF (@@FETCH_STATUS <> -2)
SET @COUNTER = 1
DECLARE INNER_CURSOR CURSOR
FOR
SELECT * FROM CONTACTS
WHERE CONTACT_ID = @CONTACT_ID
AND TYPE = @TYPE
FOR UPDATE
OPEN INNER_CURSOR
FETCH NEXT FROM INNER_CURSOR
WHILE (@@FETCH_STATUS <> -1)
BEGIN
IF (@@FETCH_STATUS <> -2)
UPDATE CONTACTS
SET INDEX_NO = @COUNTER
WHERE CURRENT OF INNER_CURSOR
SET @COUNTER = @COUNTER + 1
FETCH NEXT FROM INNER_CURSOR
END
CLOSE INNER_CURSOR
DEALLOCATE INNER_CURSOR
FETCH NEXT FROM OUTER_CURSOR INTO @CONTACT_ID, @TYPE, @INDEX_NO
END
CLOSE OUTER_CURSOR
DEALLOCATE OUTER_CURSOR
COMMIT TRAN