View or Function '' Is Not Updatable Because the Modification Affects Multiple Base Tables

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

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.

View or function [view name] is not updatable because the modification affects multiple base tables - On POST to any and every controller

Look at your DI. This could be caused by leaking/sharing DbContext instances. If the POST controller gets a DbContext instance with a tracked, altered SoaHeader object, it would try to save it when you call SaveChanges.

Unable to delete from CTE - MSG 4405

Old question but here is a way to do this with a cte without getting the multiple base tables error:

;with del as (
    SELECT t1.*
    FROM t1
    WHERE
        t1.tid in (select t2.tid from t2)
)
DELETE del;

By using the in comparison it will be slower than a join but it allows you to have a single base table. Also, it's worth noting you can do this same type of delete without a cte entirely:

DELETE FROM t1
where t1.tid in (select t2.tid from t2);

and (best performance):

DELETE t1
FROM t1
INNER JOIN t2
    ON T1.tid = t2.tid;

How can I DELETE from a CTE in which a JOIN exists?

JOIN won't work in CTE/DELETE, try to restructure CTE query to use WHERE EXISTS () instead of JOIN like here:

http://stevestedman.com/2015/06/deleting-from-a-cte-with-an-exists-statement/

Delete from CTE with join

How select cte update multiple tables column one time?

You can only update one table a time. Using cte or view does not change this. Update one table and then the other

update A set [Value] = 'v1' where A.ID = '1111';
update B set Custom_value='v2' where B.ID = '1111';

to avoid reading partially updated data, use transaction and appropriate isolation level

---

Related Topics