Using an Aggregate In Merge Update Statement

Question

I am attempting to run a COUNT() statement in an update statement. However my syntax presents this error

Msg 157, Level 15, State 1, Line 6<br>
An aggregate may not appear in the set list of an UPDATE statement.

What do I need to change in my syntax so this statement will execute succesfully?

MERGE masterdata md
USING receivedData rd
ON md.empName = rd.empName
WHEN MATCHED THEN
UPDATE
SET md.numberOfSales = COUNT(rd.SaleID);

Lennart - Slava Ukraini · Accepted Answer · 2017-04-29T14:04:15.427

I cant test this right now, but doing the aggregate in the USING clause should work:

MERGE INTO masterdata md
USING (
    SELECT empName, COUNT(SaleID) as CNT
    FROM receivedData
    GROUP BY empName
) rd
    ON md.empName = rd.empName
WHEN MATCHED THEN
    UPDATE SET md.numberOfSales = rd.CNT;

One powerful aspect of the relational model is that it is closed under select, join, union and so forth. Closed in the sense that the result of a query is a relation (or table in SQL). Therefor it is easy to form new derived relations that can be used in place of a table.

Aaron Bertrand · Answer 2 · 2020-05-07T21:45:46.933

Not sure why you're using MERGE for this. This would normally just be an update with a join, but it is complicated because of the aggregate (I hope that receivedData is part of some ETL process and not permanent data; if it is the latter then you're just storing redundant data for no clear reason).

With transactional control and error handling set aside:

;WITH x AS
(
  SELECT empName, c = COUNT(SaleID) FROM dbo.receivedData
)
UPDATE md SET numberOfSales = x.c
  FROM dbo.masterdata AS md
  INNER JOIN x
  ON x.empName = md.empName;

INSERT dbo.masterdata(empName, NumberOfSales)
  SELECT empName, COUNT(SaleID) 
  FROM dbo.receivedData AS r
  WHERE NOT EXISTS (SELECT 1 FROM dbo.masterdata
    WHERE empName = r.empName)
  GROUP BY empName;

MERGE may look like an easier way to solve this problem (though your question didn't talk about non-matches at all), but as I point out in this article, there are much bigger problems you need to be wary of.

See another more detailed answer that talks about this alternative to MERGE for this use case:

Can I optimize this MERGE statement?

Also please always use schema names in all object references.

Also, be very careful about case - either your names are sloppy (masterdata vs. receivedData) or your code is, and this can lead to problems too.

Using an Aggregate In Merge Update Statement

2 Answers2