Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

Tags:

sql

sql-server

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

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

I've read that I need to accomplish this using a subquery, but need help. Thanks

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;

Update: Tried the proposed solution

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;

Unfortunately this affects 0 rows/doesn't work. I think this is because HID is a foreign key (in tbl8_update_transactions). 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.

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)
);

I think this might solve the null foreign key issue if I can get help with it

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

In case you want to see my C# code that performs the insert successfully, but doesn't populate the foreign key

 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);
    }
like image 425
Dave Avatar asked Sep 19 '14 15:09

Dave


People also ask

Can we use aggregate function in UPDATE?

An aggregate may not appear in the set list of an UPDATE statement. But SQL doesn't always agree that it should be simple. Let's setup a contrived example using a data set of Airport Gate information from San Francisco Airport.

What does the UPDATE clause in an UPDATE statement do?

The UPDATE command in SQL is used to modify or change the existing records in a table. If we want to update a particular value, we use the WHERE clause along with the UPDATE clause.

Can we use UPDATE in SELECT statement?

The subquery defines an internal query that can be used inside a SELECT, INSERT, UPDATE and DELETE statement. It is a straightforward method to update the existing table data from other tables. The above query uses a SELECT statement in the SET clause of the UPDATE statement.


1 Answers

YOu can write the query like this:

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;

I find it confusing to use a from clause and not have the main table mentioned there. I prefer writing this as:

UPDATE ut
    SET HID = h.maxHID
    FROM tbl8_update_transactions ut CROSS JOIN
         (select max(HID) as maxHID from HOLIDAY_DATE_TABLE) h
    WHERE ut.HID IS NULL;
like image 182
Gordon Linoff Avatar answered Sep 22 '22 07:09

Gordon Linoff