UPDATE all records from existing SELECT query

I have query to select data from related tables.

SELECT 
    s.id,
    CASE
       WHEN count(DISTINCT e.id) <> 0 
          THEN count(DISTINCT o.id) / count(DISTINCT e.id)
    END OrdersAverageNumber
FROM
    [School] s
JOIN 
    [SchoolStore] ss ON ss.SchoolId = s.Id
JOIN 
    [Event] e ON e.SchoolId = ss.SchoolId
              AND e.IsDeleted = 0
              AND e.Status = 1
              AND e.Date >= @startDate
              AND e.Date <= @endDate
JOIN 
    [Order] o ON o.EventId = e.id
              AND o.OrderStatus = 1
              AND o.CreatedDate >= @startDate
              AND o.CreatedDate <= @endDate
GROUP BY 
    s.id;

But I can’t understand what I need to change to update all OrdersAverageNumber records in School table with values from selection above.

Answer

You can use update:

with q as (< your query here >)
update s
    set OrdersAverageNumber = q.OrdersAverageNumber
    from school s join
         q
         on s.id = q.id;

Advertisement

Answer