SQL更新查询 - 聚合可能不会出现在UPDATE语句的集合列表中

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

我正在尝试使用HOLIDAY_DATE_TABLE中最高的主键值(HID)来编写更新tbl8_update_transactions HID字段(其中为null)的查询。我收到以下错误

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

“聚合可能不会出现在UPDATE语句的集合列表中”

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.

不幸的是,这会影响0行/不起作用。我认为这是因为HID是一个外键(在tbl8_update_transactions中)。真正的问题似乎是我的C#方法,用于将记录插入表中(它插入行而不填充外键)。我想用触发器而不是C#代码处理它。我的表格如下。

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

我认为如果我能得到帮助,这可能会解决null外键问题

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

如果您想查看成功执行插入的C#代码,但不填充外键

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

4 个解决方案

#1

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:

我发现使用from子句并没有提到那里的主表令人困惑。我更喜欢这样写:

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;

#2

It turns out that you can use aggregates in an UPDATE statement by using this format instead:

事实证明,您可以使用此格式在UPDATE语句中使用聚合:

;WITH [ProjectHoursTotal] AS ( 
    SELECT 
        ProjectId = [ProjectHours].[ProjectId] 
        , FirstActivity = MIN([ProjectHours].[EntryDate]) 
        , LastActivity = MAX([ProjectHours].[EntryDate]) 
        , TotalHours = SUM([ProjectHours].[TotalHours]) 
    FROM 
        [dbo].[ProjectHours] 
    GROUP BY 
        [ProjectHours].[ProjectId] 
) 
UPDATE 
    [dbo].[Project] 
SET 
    [Project].[FirstActivity] = [ProjectHoursTotal].[FirstActivity] 
    , [Project].[LastActivity] = [ProjectHoursTotal].[LastActivity] 
    , [Project].[TotalHours] = [ProjectHoursTotal].[TotalHours] 
FROM 
    [dbo].[Project] 
    INNER JOIN [ProjectHoursTotal] ON [Project].[ProjectId] = [ProjectHoursTotal].[ProjectId]

The semicolon is not a typo.

分号不是拼写错误。

Super fast and super easy!

超快速,超级简单!

#3

I guess, your proposed code is correct, just missed SELECT

我猜,你提出的代码是正确的,只是错过了SELECT

UPDATE tbl8_update_transactions
 SET HID = (SELECT h.maxHID
FROM (select max(HOLIDAY_DATE_TABLE.HID) as maxHID from HOLIDAY_DATE_TABLE) h
WHERE tbl8_update_transactions.HID IS NULL);

#4

-5

SELECT is missing.

缺少SELECT。

You wrote

MAX(HOLIDAY_DATE_TABLE.HID) FROM HOLIDAY_DATE_TABLE

You probably meant

你可能意味着

SELECT MAX(HOLIDAY_DATE_TABLE.HID) FROM HOLIDAY_DATE_TABLE

#1