Aggregate Functions in UPDATE: What SQL Allows

Usually, no: SQL does not allow an aggregate such as AVG() or SUM() directly as a row-level value in an UPDATE assignment. You can still update rows using aggregate results by calculating them in a subquery, a grouped derived table, or a common table expression, then connecting those results to the rows being updated.

Why aggregates and UPDATE work differently

An UPDATE changes values in rows that already exist. Its SET clause ordinarily assigns a value for each target row, while an aggregate function summarizes a set of rows into a value, such as one average or total. Those operations have different levels of meaning: an UPDATE is row-oriented, and an aggregate is set-oriented.

For example, this looks like a natural way to set every employee’s salary to the department average, but it is not valid in mainstream SQL systems:

UPDATE Employees
SET salary = AVG(salary);

The database cannot tell which rows should contribute to the average or whether you want one average for the whole table or a separate average for each department. The query needs to define that calculation and how its result corresponds to each row. A subquery or grouped result makes those relationships explicit.

This restriction is not the same as saying aggregates can never be used while updating data. They are commonly used in the query that supplies the new value. The important distinction is between writing an aggregate directly into the row-level UPDATE expression and using a query to calculate an aggregate that the UPDATE can consume.

Where aggregate functions are normally allowed

Aggregates such as COUNT(), SUM(), AVG(), MIN(), and MAX() are normally used in a SELECT list, in a HAVING condition, or inside a subquery. A GROUP BY clause can produce one result per group. The UPDATE can then use the resulting value through a scalar subquery or a join.

A common mistake is trying to put an aggregate directly in a WHERE condition, as in WHERE salary > AVG(salary). A WHERE condition is evaluated for candidate rows, before grouping in the usual query-processing model; it does not have a group average available there. To compare rows with an average, calculate that average in a subquery and compare against its result. HAVING is for filtering grouped results, not for directly filtering individual rows in an UPDATE.

There are also expressions that look like aggregates but are not group aggregates. For instance, a window function such as AVG(salary) OVER (PARTITION BY department_id) returns a value associated with each row rather than collapsing a group to one row. Whether and how a window function can supply an UPDATE value depends on the database, and it is often clearer to put the window calculation in a CTE or derived table first.

Use a scalar subquery for a single result per row

If each employee should receive the average salary for their own department, a correlated subquery expresses the relationship: the inner query calculates an average for the department belonging to the current outer row. The subquery must return one value for each row being updated.

UPDATE Employees AS e
SET salary = (
    SELECT AVG(e2.salary)
    FROM Employees AS e2
    WHERE e2.department_id = e.department_id
);

In databases that accept this alias syntax, the inner query is evaluated in relation to each target row. The department_id match restricts the input set, and AVG returns one scalar value for that department. Check your database’s UPDATE syntax before using this exact form: MySQL, in particular, has restrictions on reading from the target table in a subquery, and alias rules vary by product.

Also consider what this statement means for the data. It sets every salary in a department to the same average, including the rows whose salaries contributed to that average. That may be intentional, but it is not the same as giving each person a raise equal to the average, or comparing salaries to the average. Write down the intended rule before choosing the expression.

Update from grouped results

For a large or more complex update, it is often easier to calculate one result per key and join that result to the target table. Here is a PostgreSQL-style UPDATE using a grouped derived table:

UPDATE Employees AS e
SET salary = d.avg_salary
FROM (
    SELECT department_id, AVG(salary) AS avg_salary
    FROM Employees
    GROUP BY department_id
) AS d
WHERE d.department_id = e.department_id;

The derived table produces one row per department, containing its average salary. The UPDATE matches each employee to the department result and assigns that average. This style is useful when the calculation uses several aggregates or when you want to inspect the grouped query separately before updating.

The matching key matters. The grouped result should have no more than one row for each target row. If the join matches a target row to multiple result rows, the update may fail or produce a result that is not reliably defined, depending on the database. Grouping by the same key used in the join is a common way to ensure a single summary value per group.

Syntax differs among SQL databases

SQL products share the general idea of calculating an aggregate separately, but their UPDATE syntax is not identical. A query copied from one database may need changes before it runs in another.

PostgreSQL: supports UPDATE … FROM, as shown above. The FROM clause supplies the grouped result, and the WHERE clause connects it to the target alias.
SQL Server: uses UPDATE with a FROM clause, but the target table is normally named in that FROM clause. For example, the shape is UPDATE e SET salary = d.avg_salary FROM dbo.Employees AS e JOIN (SELECT department_id, AVG(salary) AS avg_salary FROM dbo.Employees GROUP BY department_id) AS d ON d.department_id = e.department_id. Use the actual schema and table names in your database.
MySQL: commonly uses joined-update syntax, with the JOIN before SET: UPDATE Employees AS e JOIN (SELECT department_id, AVG(salary) AS avg_salary FROM Employees GROUP BY department_id) AS d ON d.department_id = e.department_id SET e.salary = d.avg_salary. MySQL’s restrictions on selecting from a table being updated can affect other query shapes, so test the exact statement against the MySQL version in use.
SQLite: supports UPDATE FROM in SQLite 3.33.0 and later. Earlier versions can use a scalar subquery, subject to SQLite’s syntax and behavior.

These examples illustrate the pattern, not a universal statement that every SQL engine accepts every form. Consult the documentation for your database and version, especially if the update reads from the same table it changes.

Choose the update pattern that fits the calculation

Use a scalar subquery when the intended value is naturally one result for the current row, such as a department average selected by that row’s department_id. Use a grouped derived table or CTE when you want to summarize many groups once and join those summaries back to the target rows. A CTE can make a long update easier to read, but its availability and the syntax for attaching it to UPDATE vary by database.

Before choosing, check these details:

What is the exact group being summarized: the entire table, one department, one customer, or another key?
Does every target row have a corresponding aggregate result?
Can the aggregate query return more than one row for a target row?
What should happen when a group has no matching rows or contains only NULL values?
Does the database allow the target table to appear in the aggregate subquery or join in this form?

For example, AVG ignores NULL inputs. If every salary in a department is NULL, AVG(salary) returns NULL. Assigning that result will replace the target salary with NULL if the column permits it; otherwise, the update may fail. Decide whether that is right, or use an explicit fallback such as COALESCE where appropriate.

Common errors and ways to prevent them

An “aggregate function is not allowed” or “invalid use of group function” message usually means an aggregate appears at a query level where the database expects a row-level expression. Move the aggregate into a SELECT subquery or grouped query, then reference its scalar result or join to it. The exact error wording depends on the engine.

Another common problem is a scalar subquery returning more than one row. A scalar assignment needs one value, not a list of group values. Add a condition that identifies a single group, or aggregate/group the data so it returns one row. Do not solve this by arbitrarily selecting a row unless choosing that row is genuinely part of the business rule.

Missing groups can cause a scalar subquery to return NULL, while an inner join to a grouped result can leave unmatched target rows untouched. Those outcomes differ. If unmatched rows should be set to a specific value, account for them deliberately, for example with an outer join or a suitable fallback expression in a dialect that supports the chosen syntax.

Before running a production update, run the aggregate query by itself and inspect its group keys and values. Then preview the intended row-to-value mapping with a SELECT that uses the same join or correlation. If practical, perform the update in a transaction, verify affected rows and results, and commit only after checking them. A WHERE clause that limits the update to the intended population is essential; omitting it can change every row.

Can a window function be an alternative?

A window function can calculate a group statistic while retaining individual rows. Conceptually, AVG(salary) OVER (PARTITION BY department_id) produces a department average alongside each employee row. This can be helpful when the update needs row-level detail as well as a group statistic, such as setting a flag based on how a person’s value compares with the department average.

However, window functions are not a universal way to write an UPDATE. Some databases restrict them in the UPDATE expression or require the calculation to appear in a subquery or CTE. A derived result with a stable key is often the most portable way to show the mapping. Confirm support and syntax for the specific database before relying on a window-based update.

Frequently Asked Questions

Can I use SUM() directly in an UPDATE statement?
Usually not directly in SET as a group aggregate. Calculate SUM() in a subquery or grouped result, then assign or join its result to the rows being changed.

Can I use an aggregate in the UPDATE WHERE clause?
Not ordinarily as a direct row-level condition. Put the aggregate in a subquery and compare the row’s value with the subquery result, or use a grouped query suited to the database.

Can I update a column with the average for its group?
Yes. Calculate AVG() per group and connect each target row to its group, using a correlated scalar subquery or a grouped table joined to the update.

Why does my aggregate subquery return more than one row?
It is likely returning one row per group, while the assignment expects one scalar value. Restrict it to the target row’s group or join the grouped results to the target rows instead.

Will the average include the value being updated?
In the examples above, the aggregate reads the table’s existing values as part of its input, so the current row contributes when it meets the group condition. If your intended calculation must exclude that row or use a different set of rows, encode that rule explicitly.

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.