title: How to use aggregate in the set list of an UPDATE statement
body: <p>Let me describe the whole situation: I have below two tables. The values of <code>MaxTestCasesExecutedBySingleUser</code> and <code>TotalTestCasesExecutedByAllUser</code> in <code>Module</code> table needs to be updated using some Aggregate clause</p>
  
  <pre><code>CREATE TABLE [dbo].[Module](
      [ProjectID] [int] NOT NULL,
      [ModuleID] [int] NOT NULL,
      [ModuleName] [nvarchar](100) NOT NULL,
      [MaxTestCasesExecutedBySingleUser] [int] NULL,
      [TotalTestCasesExecutedByAllUser] [int] NULL,
      PRIMARY KEY ([ProjectID],[ModuleID]))
  
  CREATE TABLE [dbo].[ModuleMember](
      [ProjectID] [int] NOT NULL,
      [ModuleID] [int] NOT NULL,
      [SerialNo] [int] NOT NULL,
      [Name] [nvarchar](100) NOT NULL,
      PRIMARY KEY ([ProjectID],[ModuleID],[SerialNo]))
  
  Insert Into Module Values (1,1,'Installation_Test',null,null)
  Insert Into Module Values (1,2,'Server_Test',null,null)
  Insert Into Module Values (1,3,'Client_Test',null,null)
  Insert Into Module Values (1,4,'Security_Test',null,null)
  
  Insert Into ModuleMember Values(1,1,0,'Jim')
  Insert Into ModuleMember Values(1,1,1,'Bob')
  Insert Into ModuleMember Values(1,2,0,'Jack')
  Insert Into ModuleMember Values(1,2,1,'Steve')
  Insert Into ModuleMember Values(1,2,2,'Roy')
  Insert Into ModuleMember Values(1,2,3,'Jerry')
  Insert Into ModuleMember Values(1,3,0,'Root')
  Insert Into ModuleMember Values(1,3,1,'Tom')
  Insert Into ModuleMember Values(1,4,0,'Evil')
  </code></pre>
  
  <p>There is another Table Valued Function <code>dbo.GetValue</code> which takes <code>Name</code> (from <code>ModuleMember</code> table) as parameter and returns the values of <code>Name</code> and <code>TestCasesExecutedByTheUser</code>  in the form of a  table. I need to update these two values in <code>Module</code> table. </p>
  
  <p><strong>Definition:</strong></p>
  
  <p>MaxTestCasesExecutedBySingleUser = maximum number if test case executed by a member in a module</p>
  
  <p>TotalTestCasesExecutedByAllUser = total number of test cases executed by all member in a module.</p>
  
  <p>I tried below query but it is throwing me error : <code>An aggregate may not appear in the set list of an UPDATE statement.</code></p>
  
  <pre><code>UPDATE M
  SET 
  M.MaxTestCasesExecutedBySingleUser = MAX(N.TestCasesExecutedByTheUser),
  M.TotalTestCasesExecutedByAllUser = SUM(N.TestCasesExecutedByTheUser)
  FROM Module M 
  JOIN ModuleMember Mem ON (M.ProjectID = Mem.ProjectID AND M.ModuleID = Mem.ModuleID)
  CROSS APPLY dbo.GetValue(Mem.Name) N
  </code></pre>