SQL 视图或函数 '' 不可更新，因为修改影响多个基表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/33185999/
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
View or function '' is not updatable because the modification affects multiple base tables
提问by Bhav
I'm attempting to create a sql script that should check to see if a row exists. If one doesn't exist, I want to create one and if one does exist, I want to update it.
我正在尝试创建一个 sql 脚本，该脚本应该检查一行是否存在。如果一个不存在，我想创建一个，如果一个存在，我想更新它。
However, in my code below, the INSERT line throws the following error:
但是，在我下面的代码中，INSERT 行会引发以下错误：
View or function '' is not updatable because the modification affects multiple base tables.
视图或函数 '' 不可更新，因为修改会影响多个基表。
Is there a way to find out what other base tables this would affect and how to achieve my objective?
有没有办法找出这会影响哪些其他基表以及如何实现我的目标？
SQL code:
SQL 代码：
IF NOT EXISTS (SELECT * FROM g4b_stockcountsummary
WHERE g4b_stockcountid = @scid AND g4b_protoproductid = @ppid)
BEGIN
--stock count data doesn't exist for the given product/stock count, create new record
SET @difference = @count - @expectedtotal
INSERT INTO g4b_stockcountsummary (g4b_stockcountsummaryid, g4b_stockcountid, g4b_protoproductid, g4b_expectedtotal, g4b_counttotal, g4b_difference)
VALUES (NEWID(), @scid, @ppid, @expectedtotal, @count, @difference)
END
ELSE
BEGIN
--stock count data already exists for the given product/stock count, update record
DECLARE @originalcount INT
SET @originalcount = (SELECT g4b_counttotal FROM g4b_stockcountsummary
WHERE g4b_stockcountid = @scid AND g4b_protoproductid = @ppid)
SET @count = @originalcount + @count
SET @difference = @count - @expectedtotal
UPDATE g4b_stockcountsummary
SET g4b_expectedtotal = @expectedtotal,
g4b_counttotal = @count,
g4b_difference = @difference
WHERE g4b_stockcountid = @scid
AND g4b_protoproductid = @ppid
END
回答by Gordon Linoff
g4b_stockcountsummaryis a view. Views can beupdatable, but only under certain conditions. These are listed in the documentationand they start:
g4b_stockcountsummary是一种观点。视图可以更新，但只能在特定条件下进行。这些在文档中列出，它们开始：
Updatable Views
You can modify the data of an underlying base table through a view, as long as the following conditions are true:
- Any modifications, including
UPDATE,INSERT, andDELETEstatements, must reference columns from only one base table.
可更新的视图
您可以通过视图修改底层基表的数据，只要满足以下条件即可：
- 任何修改，包括
UPDATE、INSERT和DELETE语句，都必须仅引用一个基表中的列。
Hence, you cannot do what you want. You either need to fix the view or update each base table independently.
因此，你不能做你想做的。您需要单独修复视图或更新每个基表。
I should point out that lad2025 is correct. You can use an instead oftrigger on the view to support the update. The documentation is referring to the base update on the view.
我应该指出 lad2025 是正确的。您可以instead of在视图上使用触发器来支持update. 该文档指的是视图上的基本更新。
