SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 How to update Table based off an SELECT statement?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

mgreen84
Yak Posting Veteran

62 Posts

Posted - 03/06/2014 :  18:15:52  Show Profile  Reply with Quote
I know you can INSERT to a table based off a SELECT statement. But can you do the same with a UPDATE, and if so can you provide assitance?

The below query returns all the fields from my table called 'Summary.tblResult', base off the results from the select I need to update the 'Rating' field with the returned value. How can I achieve this?


SELECT Emplevel
	  ,metric.DateType  
	  ,StartDate
	  ,metric.EmployeeID
	  ,Metric.PAGroupID 
	  ,Metric.ObjectiveID
	  ,Result
	  ,CASE WHEN Metric.Result >= obj.Leading 
		     THEN pa.LeadingValue 
		    
		    WHEN Metric.Result >= obj.Performing 
		     AND  Metric.Result < ISNULL(obj.LEADING, Metric.Result+ 1)  -- if leading is null sets condition to true 
		     THEN pa.PerformingValue
		    
		    WHEN  Metric.Result < obj.Performing 
		     THEN pa.DevelopingValue
		 ELSE NULL 
		END Rating

		     
	  FROM Summary.tblResult         METRIC 
	  
	  INNER JOIN Config.tblObjective OBJ 
	        ON Metric.ObjectiveID = obj.ObjectiveID
	  
					                
	  INNER JOIN Config.tblOveralls  PA
			ON Metric.PAGroupID = pa.PAGroupID
	
	  WHERE obj.BestScore_HighLowRange = 'H'
		AND obj.MonthlyRating = 0
		AND BlendedRating = 0
		AND Metric.ObjectiveID = @objectiveid
		AND Metric.DateType = @Datetype
		AND StartDate BETWEEN @StartDate AND @EndDate


thanks

tkizer
Almighty SQL Goddess

USA
36601 Posts

Posted - 03/06/2014 :  18:28:19  Show Profile  Visit tkizer's Homepage  Reply with Quote

update METRIC 
set rating = CASE WHEN Metric.Result >= obj.Leading 
		     THEN pa.LeadingValue 
		    
		    WHEN Metric.Result >= obj.Performing 
		     AND  Metric.Result < ISNULL(obj.LEADING, Metric.Result+ 1)  -- if leading is null sets condition to true 
		     THEN pa.PerformingValue
		    
		    WHEN  Metric.Result < obj.Performing 
		     THEN pa.DevelopingValue
		 ELSE NULL 
		END 
FROM Summary.tblResult         METRIC 
	  
	  INNER JOIN Config.tblObjective OBJ 
	        ON Metric.ObjectiveID = obj.ObjectiveID
	  
					                
	  INNER JOIN Config.tblOveralls  PA
			ON Metric.PAGroupID = pa.PAGroupID
	
	  WHERE obj.BestScore_HighLowRange = 'H'
		AND obj.MonthlyRating = 0
		AND BlendedRating = 0
		AND Metric.ObjectiveID = @objectiveid
		AND Metric.DateType = @Datetype
		AND StartDate BETWEEN @StartDate AND @EndDate


Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

mgreen84
Yak Posting Veteran

62 Posts

Posted - 03/07/2014 :  10:58:30  Show Profile  Reply with Quote
Thanks tkizer for your relpy. Question though, can this also be acheived with a MERGE or CTE? During my searching I kept getting results saying to use those.

quote:
Originally posted by tkizer


update METRIC 
set rating = CASE WHEN Metric.Result >= obj.Leading 
		     THEN pa.LeadingValue 
		    
		    WHEN Metric.Result >= obj.Performing 
		     AND  Metric.Result < ISNULL(obj.LEADING, Metric.Result+ 1)  -- if leading is null sets condition to true 
		     THEN pa.PerformingValue
		    
		    WHEN  Metric.Result < obj.Performing 
		     THEN pa.DevelopingValue
		 ELSE NULL 
		END 
FROM Summary.tblResult         METRIC 
	  
	  INNER JOIN Config.tblObjective OBJ 
	        ON Metric.ObjectiveID = obj.ObjectiveID
	  
					                
	  INNER JOIN Config.tblOveralls  PA
			ON Metric.PAGroupID = pa.PAGroupID
	
	  WHERE obj.BestScore_HighLowRange = 'H'
		AND obj.MonthlyRating = 0
		AND BlendedRating = 0
		AND Metric.ObjectiveID = @objectiveid
		AND Metric.DateType = @Datetype
		AND StartDate BETWEEN @StartDate AND @EndDate


Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36601 Posts

Posted - 03/07/2014 :  11:56:34  Show Profile  Visit tkizer's Homepage  Reply with Quote
Are you trying to insert if it doesn't exist and update if it does?

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000