CODEWITHSUNDEEP
sqlsql-servert-sqlsql-merge
Bogdan Pușcașu
Bogdan Pușcașu

Reputation: 575

SQL - An aggregate may not appear in the set list of an UPDATE statement

Hello and good day to everyone. Could you help me correct this SQL sequence?

MERGE INTO table2 WITH (HOLDLOCK) AS target
USING
(
  SELECT column1,
         AccessDate,
         AccessCount,
         column4,
         column5, 
         column6, 
         column7, 
         column8
  FROM  table1
  GROUP BY column1, column4, column5, column6, column7, column8
) AS source
ON target.column1 = source.column1 AND
   target.column5 = source.column5 AND
   target.column6 = source.column6 AND
   target.column7 = source.column7 AND
   target.column8 = source.column8
WHEN MATCHED THEN 
UPDATE SET target.LastAccessDate = MAX(source.AccessDate),
   target.LastWeeklyAccessCount = (SELECT SUM(source.AccessCount))
WHEN NOT MATCHED BY TARGET THEN
   INSERT (column1, LastAccessDate, LastWeeklyAccessCount, column4, column5, column6, column7, column8)
    VALUES (source.column1, source.AccessDate, source.AccessCount, source.column4, source.column5, source.column6, source.column7, source.column8);

I'm getting an "aggregate may not appear in the set list of an UPDATE statement" error when I try to run it because of these two lines:

target.LastAccessDate = MAX(source.AccessDate),
target.LastWeeklyAccessCount = (SELECT SUM(source.AccessCount))

My code should update in target the AccessDate to be the max of AccessDates from in the group and the LastWeeklyAccessCount to be the sum of counts in the group.

When no matching records are found just insert. I've worked with SQL like 2 years ago but can't remember much so any help is aprreciated.

Upvotes: 2

Views: 1069

Answers (1)

Nolan Shang
Nolan Shang

Reputation: 2328

The error is because AccessDate,AccessCount in select column list of source statement not appear in group by list:

    MERGE INTO table2 WITH (HOLDLOCK) AS target
    USING
    (
      SELECT column1,
             MAX(AccessDate) AS AccessDate,
             SUM(AccessCount) AS AccessCount,
             column4,
             column5, 
             column6, 
             column7, 
             column8
      FROM  table1
      GROUP BY column1, column4, column5, column6, column7, column8
    ) AS source
    ON target.column1 = source.column1 AND
       target.column5 = source.column5 AND
       target.column6 = source.column6 AND
       target.column7 = source.column7 AND
       target.column8 = source.column8
    WHEN MATCHED THEN 
    UPDATE SET target.LastAccessDate = source.AccessDate,
       target.LastWeeklyAccessCount = source.AccessCount
    WHEN NOT MATCHED BY TARGET THEN
       INSERT (column1, LastAccessDate, LastWeeklyAccessCount, column4, column5, column6, column7, column8)
        VALUES (source.column1, source.AccessDate, source.AccessCount, source.column4, source.column5, source.column6, source.column7, source.column8);

Upvotes: 2

Related Questions