update with the value returned from aggregate function

Question

How to update a column of a table using an aggregate function in the sql update statement ?

Answer 1

Aggregate function, by definition, aggregates one or more records of the input into a single record in a resultset, so it is not obvious which one you want to update.

In general, you can use aggregate functions in a subquery:

UPDATE  mytable
SET     mycol = 
        (
        SELECT  SUM(othercol)
        FROM    othertable o
        WHERE   o.yetothercol = m.yetmycol
        )

, in a JOIN (works in MySQL and SQL Server)

UPDATE  mytable
JOIN    (
        SELECT  yetothercol, SUM(othercol) AS psum
        FROM    othertable
        GROUP BY
                yetothercol
        ) s
ON      yetmycol = yetothercol
SET     mycol = psum

, or in a MERGE statement (works in Oracle and SQL Server 2008):

MERGE
INTO    mycol
USING   (
        SELECT  yetothercol, SUM(othercol) AS psum
        FROM    othertable
        GROUP BY
                yetothercol
        ) s
ON      (yetmycol = yetothercol)
WHEN MATCHED THEN
UPDATE
SET     mycol = psum