Cacher is the code snippet organizer for pro developers

DECLARE @tbl TABLE (
	Id INT identity(1, 1)
	,uccps INT
	)

INSERT INTO @tbl (uccps)
SELECT UserCenterXCenterPguSkillId
FROM Assessment a
WHERE a.AssessmentName = 'A&LR'
	AND a.IsActive = 1
GROUP BY UserCenterXCenterPguSkillId
HAVING COUNT(assessmentId) > 1

SELECT *
FROM @tbl

DECLARE @i INT = 1
DECLARE @rCount INT = 0

SELECT @rCount = COUNT(1)
FROM @tbl

DECLARE @Luccps INT = 0
DECLARE @assessmentId INT

WHILE (@i <= @rCount)
BEGIN
	SELECT @Luccps = uccps
	FROM @tbl
	WHERE Id = @i;

	SELECT TOP 1 @assessmentId = AssessmentId
	FROM Assessment
	WHERE UserCenterXCenterPguSkillId = @Luccps
		AND AssessmentStatusId IN (1, 3)

	IF (@assessmentId > 0)
	BEGIN
		UPDATE Assessment
		SET IsActive = 0
		WHERE AssessmentId = @assessmentId
	END

	SET @assessmentId = 0
	SET @i = @i + 1;
END