Update from select, Update only existing records avoid looping
IF @IsAssigning = 0
BEGIN
UPDATE ProductMapping
SET IsActive = 0, ModifiedBy = @UserId, ModifiedDate = GETDATE()
FROM ProductMapping
INNER JOIN @tblProducts tbl ON ProductMapping.ProductId = tbl.ProductId
AND ProductMapping.ProductSeriesId = tbl.ProductSeriesId
WHERE tbl.ProductId IN (
SELECT PM.ProductId
FROM ProductMapping PM WITH (NOLOCK)
WHERE pm.OrganizationId = @OrganizationId
AND PM.IsActive = 1
--AND pm.CenterXPGUXSkillId = @SkillId
)
END
Or
UPDATE dbo.TCIAssessmentResultSummary
SET AssessmentMode = 'NA', UserId = b.UserId, ActivityId = b.ActivityId, Rating = b.Rating
FROM TCIAssessmentResultSummary a
INNER JOIN #TEMP b WITH (NOLOCK) ON a.UserId = b.UserId
AND a.ActivityId = b.ActivityId
WHERE EXISTS (
SELECT 1
FROM TCIAssessmentResultSummary etbl
WHERE etbl.Isactive = 1
AND etbl.UserId = b.UserId
AND etbl.ActivityId = b.ActivityId
)
or --Best One --Why Checking
UPDATE EngineerResponsibleProductsXActivityRating
SET Rating = b.Rating,IsEndorsed = 0,AssessmentModeId =1,ModifiedDate=GETDATE()
FROM EngineerResponsibleProductsXActivityRating a
INNER JOIN @Temp2 b ON a.UserId = b.UserId
AND a.OrganizationId = b.OrganizationId
AND a.ProductId = b.ProductId
AND a.ActivityId = b.ActivityXTypeXPortfolioId
where a.IsActive = 1