带有连接表的 SQL 可更新视图 [英] SQL Updatable View with joined tables

问题描述

我有一个与此类似的视图，

I have a view that looks similar to this,

SELECT  dbo.Staff.StaffId, dbo.Staff.StaffName, dbo.StaffPreferences.filter_type
FROM    dbo.Staff LEFT OUTER JOIN
        dbo.StaffPreferences ON dbo.Staff.StaffId = dbo.StaffPreferences.StaffId

我正在尝试更新 StaffPreferences.filter_type 使用，

I'm trying to update StaffPreferences.filter_type using,

UPDATE vw_Staff SET filter_type=1 WHERE StaffId=25

我在 MSDN 文章中读到过这个，

I have read this in an MSDN article,

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

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

这是否意味着我只能更新 dbo.Staff 中的字段(这是我目前所能实现的所有内容)在这种情况下，基表"的定义是否不会扩展到任何后续连接的表?

Does this mean that I can only update fields in dbo.Staff (which is all I can currently achieve) In this context does the definition of 'base table' not extend to any subsequently joined tables?

推荐答案

您的语句应该可以正常工作，因为您只修改了一个表 (StaffPreferences) 中的列.

Your statement should work just fine since you are only modifying column(s) from one table (StaffPreferences).

如果您尝试在同一更新语句中更新来自不同表的列，则会出现错误.

If you tried to update a columns from different tables in the same update statement you would get an error.

Msg 4405, Level 16, State 1, Line 7
View or function 'v_ViewName' is not updatable because the modification affects multiple base tables.

这篇关于带有连接表的 SQL 可更新视图的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！