How to update Table based off an SELECT statement? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

How to update Table based off an SELECT statement?

Author

Topic

mgreen84
Yak Posting Veteran

94 Posts

Posted - 2014-03-06 : 18:15:52

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

38200 Posts

Posted - 2014-03-06 : 18:28:19

[code]
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
[/code]

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

mgreen84
Yak Posting Veteran

94 Posts

Posted - 2014-03-07 : 10:58:30

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/

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-03-07 : 11:56:34

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/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources