Chandrashekar
1/22/2015 - 12:24 PM

Update from select, Update only existing records avoid looping

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