SQL 更新查询 - 聚合可能不会出现在 UPDATE 语句的集合列表中
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/25937315/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL Update Query - An aggregate may not appear in the set list of an UPDATE statement
提问by Dave
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 字段（其中为空）。我收到以下错误
"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 是外键（in 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
如果我能得到帮助，我认为这可能会解决空外键问题
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);
}
回答by Gordon Linoff
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 fromclause 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;
回答by siva narayana
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);
回答by Jramesh1967
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
