View or function '' is not updatable because the modification affects multiple base tables

Question

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.

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 code:

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

Answer 1

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

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, 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.

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.