Update value returned from aggregate function

Question

How to update table column using aggregate function in sql statement update?

Answer 1

The aggregation function, by definition, aggregates one or more input records into one record in the result set, so it is not obvious which one you want to update.

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

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

in JOIN(works in MySQLand 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 expression MERGE(works in Oracleand 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