视图或函数 '' 不可更新，因为修改影响多个基表 [英] View or function '' is not updatable because the modification affects multiple base tables

问题描述

我正在尝试创建一个 sql 脚本，该脚本应该检查一行是否存在.如果一个不存在，我想创建一个，如果一个存在，我想更新它.

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.

但是，在我下面的代码中，INSERT 行会引发以下错误:

However, in my code below, the INSERT line throws the following error:

视图或函数 '' 不可更新，因为修改会影响多个基表.

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 代码:

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

推荐答案

g4b_stockcountsummary 是一个视图.视图可以更新，但仅限于特定条件.这些在文档中列出，它们开始:

g4b_stockcountsummary is a view. Views can be updatable, but only under certain conditions. These are listed in the documentation and they start:

可更新的视图

可以通过视图修改底层基表的数据，如只要满足以下条件:

You can modify the data of an underlying base table through a view, as long as the following conditions are true:

• 任何修改，包括UPDATE、INSERT 和DELETE 语句，都必须只引用一个基表中的列.

• Any modifications, including UPDATE, INSERT, and DELETE statements, must reference columns from only one base table.

因此，您无法为所欲为.您需要单独修复视图或更新每个基表.

Hence, you cannot do what you want. You either need to fix the view or update each base table independently.

我应该指出 lad2025 是正确的.您可以在视图上使用 代替 触发器来支持 update.该文档指的是视图的基本更新.

I should point out that lad2025 is correct. You can use an instead of trigger on the view to support the update. The documentation is referring to the base update on the view.

这篇关于视图或函数 '' 不可更新，因为修改影响多个基表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！