title: SQL Update Query - An aggregate may not appear in the set list of an UPDATE statement
body: <p>I'm trying to write a query that updates tbl8_update_transactions HID field (where it's null) with the primary key value (HID) that's highest in HOLIDAY_DATE_TABLE. I get the following error </p>
  
  <blockquote>
    <p>"An aggregate may not appear in the set list of an UPDATE statement"</p>
  </blockquote>
  
  <p>I've read that I need to accomplish this using a subquery, but need help. Thanks </p>
  
  <pre><code>USE BillingUI;
  UPDATE tbl8_update_transactions
  SET tbl8_update_transactions.HID = MAX(HOLIDAY_DATE_TABLE.HID)
  FROM HOLIDAY_DATE_TABLE
  WHERE tbl8_update_transactions.HID = NULL;
  </code></pre>
  
  <p><strong>Update:</strong> Tried the proposed solution</p>
  
  <pre><code>UPDATE tbl8_update_transactions
  SET HID = h.maxHID
  FROM (select max(HOLIDAY_DATE_TABLE.HID) as maxHID from HOLIDAY_DATE_TABLE) h
  WHERE tbl8_update_transactions.HID IS NULL;
  </code></pre>
  
  <p>Unfortunately this affects 0 rows/doesn't work. I think this is because HID is a foreign key (in <code>tbl8_update_transactions</code>). The real issue seems to be my C# methodology for inserting the records into the table (it inserts the row without populating the foreign key). I'd like to handle it with triggers rather than C# code. My tables are as follows.</p>
  
  <pre><code>USE BillingUI;
  CREATE TABLE HOLIDAY_DATE_TABLE
  (
  HID INT IDENTITY PRIMARY KEY,
  TABLE_NUMBER nchar(2) NOT NULL,
  HOLIDAY_DATE nchar(8) NOT NULL,
  FIELD_DESCRIPTION nVARchar(43) NULL,
  );
  
  USE BillingUI;
  CREATE TABLE tbl8_update_transactions
  (
  TID INT IDENTITY PRIMARY KEY,
  TABLE_NUMBER nchar(2) NOT NULL,
  HOLIDAY_DATE nchar(8) NOT NULL,
  FIELD_DESCRIPTION nVARchar(43) NULL,
  HID int,
  FOREIGN KEY (HID) REFERENCES HOLIDAY_DATE_TABLE (HID)
  );
  </code></pre>
  
  <p>I think this might solve the null foreign key issue if I can get help with it</p>
  
  <pre><code>CREATE TRIGGER tbl8_ins
  ON HOLIDAY_DATE_TABLE
  FOR INSERT
  AS
  BEGIN
  INSERT INTO tbl8_update_transactions
  SELECT * FROM HOLIDAY_DATE_TABLE
  WHERE HID = MAX(HID);
  END
  </code></pre>
  
  <p>In case you want to see my C# code that performs the insert successfully, but doesn't populate the foreign key</p>
  
  <pre><code> public ActionResult Create()
      {
          return View();
      }
  
      //
      // POST: /Table8/Create
  
      [HttpPost]
      public ActionResult Create(HOLIDAY_DATE_TABLE holiday_date_table, tbl8_update_transactions tbl8_update_transaction)
      {
          if (ModelState.IsValid)
          {
              db.HOLIDAY_DATE_TABLE.Add(holiday_date_table);
              db.SaveChanges();
              db.tbl8_update_transactions.Add(tbl8_update_transaction);
              db.SaveChanges();
              return RedirectToAction("../Billing/HolidayDateTable");
          }
          return View(holiday_date_table);
      }
  </code></pre>