SQL UPDATE Query

Question

I have One main table( Say main) and another Temp Table( say copy). What I am trying to do is averages and Standard Deviation of each Stored proc( there are bunch of them with different version number as suffix and they all has to be treated same) from main table and update Temp table with averages and standard deviation for each day. Following is my NOT working Code....

UPDATE @CopyOfMainTable AS copy
   SET copy.overall_count_average = AVG(main.overall_count),
      copy.overall_count_stddev=StDev(main.overall_count),
      copy.redundancy_count_average =AVG (main.redundancy_count),
      copy.redundancy_count_stddev=StDev(main.redundancy_count),
      copy.not_completed_count_average AVG(main.not_completed_count),
      copy.not_completed_count_stddev=StDev(main.not_completed_count),
      copy.session_count_average= AVG(main.session_count),
      copy.session_count_stddev=StDev(main.session_count),
      copy.per_session_count_average_average = AVG (main.per_session_count_avg),
      copy.per_session_count_average_stddev = StDev(main.per_session_count_avg),
      copy.per_session_count_max_average = AVG(main.per_session_count_max),
      copy.per_session_count_max_stddev= StDev(main.per_session_count_max),
      copy.per_session_count_stddev_average=AVG(main.per_session_count_stddev),
      copy.per_session_count_stddev_stddev=StDev(main.per_session_count_stddev),
      copy.run_time_average_average =  AVG(main.run_time_avg),
      copy.run_time_average_stddev = StDev(main.run_time_avg),
      copy.run_time_max_average =AVG(main.run_time_max),
      copy.run_time_max_stddev= StDev(main.run_time_max),
      copy.run_time_stddev_average=AVG(main.run_time_stddev),
      copy.run_time_stddev_stddev=StDev(main.run_time_stddev),
      copy.run_time_core_hours_avg_average=Avg(main.run_time_core_hours_avg),
      copy.run_time_core_hours_avg_stddev=StDev(main.run_time_core_hours_avg),
      copy.run_time_core_hours_max_average=Avg(main.run_time_core_hours_max),
      copy.run_time_core_hours_max_stddev=StDev(main.run_time_core_hours_max),
      copy.run_time_core_hours_stddev_average=Avg(main.run_time_core_hours_stddev),
      copy.run_time_core_hours_stddev_stddev=StDev(main.run_time_core_hours_stddev),
      copy.run_time_peak_hours_avg_average=Avg(main.run_time_peak_hours_avg),
      copy.run_time_peak_hours_avg_stddev=StDev(main.run_time_peak_hours_avg),
      copy.run_time_peak_hours_max_average=Avg(main.run_time_peak_hours_max),
      copy.run_time_peak_hours_max_stddev=StDev(main.run_time_peak_hours_max),
      copy.run_time_peak_hours_stddev_average=Avg(main.run_time_peak_hours_stddev),
      copy.run_time_peak_hours_stddev_stddev=StDev(main.run_time_peak_hours_stddev),
      copy.run_time_min_average=Avg(main.run_time_min),
      copy.run_time_min_stddev=StDev(main.run_time_min),
      copy.run_time_core_hours_min_average=Avg(main.run_time_core_hours_min),
      copy.run_time_core_hours_min_stddev=StDev(main.run_time_core_hours_min),
      copy.run_time_peak_hours_min_average=Avg(main.run_time_peak_hours_min),
      copy.run_time_peak_hours_min_stddev=StDev(main.run_time_peak_hours_min)
   FROM dbo.database_call_tracking as main 
   WHERE(main.date < @latestDay_ToBeConsidered_Forthreshhold) AND (main.day_of_week BETWEEN 2 AND 6)  AND (main.database_call like '%'+copy.database_call+'%')--AND (main.database_call LIKE '%[_]v[1-9]%'
   Group by database_call

This is Not working at all. Can anyone Help? or may be suggest something?

Answer 1

Typically, you need a sub-query for this scenario; here's a simplified example:

UPDATE #tmp
SET #tmp.SomeValue = x.SomeValue
FROM @MyTempTable #tmp
INNER JOIN (
    SELECT blah.SomeKey, SUM(blah.Whatever) as [SomeValue]
    /* some complex query */
    GROUP BY blah.SomeKey
) x ON #tmp.SomeKey = x.SomeKey