title: Aggregate in UPDATE command
body: <p>I am trying to update all customers payment terms based on the oldest invoice.  So far I have the following code:</p>
  
  <pre><code>UPDATE [CUSTOMERS - Main]
  SET    Payterms = 0
  FROM   (SELECT Max(INVOICES.InvoiceDate) AS LastInvoice,
                 Datediff(day, Max(INVOICES.InvoiceDate), Getdate()) AS age,
                 INVOICES.CompanyName,
                 [CUSTOMERS - Main].PayTerms,
                 [CUSTOMERS - Main].CreditLimit,
                 [CUSTOMERS - Main].CompanyRegNo
          FROM   INVOICES
                 INNER JOIN [CUSTOMERS - Main]
                         ON INVOICES.CompanyName = [CUSTOMERS - Main].CompanyName
          GROUP  BY INVOICES.CompanyName,[CUSTOMERS - Main].PayTerms,[CUSTOMERS - Main].CreditLimit,[CUSTOMERS - Main].CompanyRegNo
          HAVING (Datediff(day, Max(INVOICES.InvoiceDate), Getdate()) &gt; 365)
                 AND ([CUSTOMERS - Main].PayTerms &gt; 0
                       OR [CUSTOMERS - Main].CreditLimit &gt; 0))
  </code></pre>
  
  <p>But it's not working.  How do I update a query that has an aggregate function?</p>