Aggregate Function in an SQL Update Query

Aggregate function in an SQL update query?

UPDATE t1
SET t1.field1 = t2.field2Sum
FROM table1 t1
INNER JOIN (select field3, sum(field2) as field2Sum
   from table2
  group by field3) as t2
on t2.field3 = t1.field3  

SQL Update Query with aggregate function

I suppose the code is used in a trigger on the InventoryDetails table (because it uses the special inserted table). It would have been nice to specify it in the question.

It is a limitation of SQL Server. It can be overcome by using subqueries.

UPDATE I SET Quantity=ID.Sum_Quantity
FROM Inventory I
INNER JOIN (SELECT InventoryID, SUM(Quantity) AS Sum_Quantity 
            FROM inserted GROUP BY InventoryID) ID ON I.ID=ID.InventoryID

SQL Update statement using aggregate function causing error

I would recommend:

update ft
    set final_cost = t1.down_payment 
    from final_table ft outer apply
         (select top 1 t1.*
          from table_1 t1
          where t1.car_type = ft.car_type and
                t1.date_purchased <= ft.date
          order by t1.date_purchased desc
         ) t1;

Workaround for using aggregate function in WHERE for an UPDATE statement

You can use a subquery. Like:

UPDATE brew_sessions b
SET    status = 30
FROM  (
   SELECT recipe_id, SUM(time) AS sum_time
   FROM   recipe_fermentation_steps
   GROUP  BY 1
   ) f
WHERE  b.recipe_id = f.recipe_id
AND    b.condition_date <= CURRENT_TIMESTAMP - INTERVAL '1 day' * f.sum_time;

If referential integrity is enforced with FK constraints, you do not need to involve the table recipes at all.

The approach is questionable, though. Typically, you do not write functionally dependent values or values depending on the current time into tables. Use a VIEW (or MATERIALIZED VIEW) or similar for that.

How can I update this column with an aggregate function?

Adapting this answer, I think you want:

with agg as (
    select DGAM.GameID, HomeTeamID, SUM(OREB) as OREB_Home
    from dimGames as DGAM
    join dimPerformance as DPERF on DGAM.GameID = DPERF.GAME_ID and DGAM.HomeTeamID = DPERF.TEAM_ID
    group by DGAM.GameID, HomeTeamID
)
UPDATE dimGames dg
    SET dg.OREB_home = agg.OREB_Home
FROM agg
WHERE dg.GameId = agg.GameId and dg.HomeTeamId = agg.HomeTeamId

---

Related Topics