更新视图不​​起作用

I'm working on a view which is then updated by the user. This update basically changes the value of column. But right now it doesnt let me do that and produces this :

我正在处理一个视图,然后由用户更新。此更新基本上更改了列的值。但是现在它不让我这样做并产生这个:

Update or insert of view or function '' failed because it contains a derived or constant field.

I know this is because I have a constant in the select statement but is there a way to get around it? Please help

我知道这是因为我在select语句中有一个常量但是有办法解决它吗?请帮忙

This is my code for the view

这是我的视图代码

Create view Schema.View1
as
SELECT
Convert(Varchar(20),l.jtpName) as JobType, Convert(Varchar(10),' <All> ')as SubCategory , Convert(varchar (3), Case when a.jtpName= l.jtpName and a.subName= ' <All> ' then 'Yes' else 'No' end) As AutoProcess from Schema.JobType l left join Schema.Table1 a on l.jtpName=a.jtpName
UNION
SELECT
Convert(Varchar(20),a.jtpName) as JobType, Convert(Varchar(10),a.subName) as SubCategory, Convert(varchar (3),Case when b.jtpName= a.jtpName and b.subName= a.subName then 'Yes' else 'No' end) As AutoProcess from Schema.SubCategory a left join fds.Table1 b on a.subName=b.subName

GO

Finally the update statement:

最后是更新声明:

UPDATE Schema.View1 SET AUTOPROCESS = Case WHEN AUTOPROCESS = 'Yes' Then 'No' END Where JOBTYPE = 'Transport' and SUBCATEGORY= 'Cargo'

Thank You

3 个解决方案

#1

You cannot update a column that is the result of a computation.

您无法更新作为计算结果的列。

According to MSDN, one of the conditions for a view column to be updatable is this:

根据MSDN,视图列可更新的条件之一是:

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

任何修改(包括UPDATE,INSERT和DELETE语句)都必须仅引用一个基表中的列。

• The columns being modified in the view must directly reference the underlying data in the table columns. The columns cannot be derived in any other way, such as through the following:
  • An aggregate function: AVG, COUNT, SUM, MIN, MAX, GROUPING, STDEV, STDEVP, VAR, and VARP.

  聚合函数:AVG,COUNT,SUM,MIN,MAX,GROUPING,STDEV,STDEVP,VAR和VARP。

  • A computation. The column cannot be computed from an expression that uses other columns. Columns that are formed by using the set operators UNION, UNION ALL, CROSSJOIN, EXCEPT, and INTERSECT amount to a computation and are also not updatable.

  一个计算。无法从使用其他列的表达式计算列。通过使用集合运算符UNION,UNION ALL,CROSSJOIN,EXCEPT和INTERSECT形成的列相当于计算并且也不可更新。

要在视图中修改的列必须直接引用表列中的基础数据。列不能以任何其他方式派生,例如通过以下方式:聚合函数:AVG,COUNT,SUM,MIN,MAX,GROUPING,STDEV,STDEVP,VAR和VARP。一个计算。无法从使用其他列的表达式计算列。通过使用集合运算符UNION,UNION ALL,CROSSJOIN,EXCEPT和INTERSECT形成的列相当于计算并且也不可更新。

• The columns being modified are not affected by GROUP BY, HAVING, or DISTINCT clauses.

要修改的列不受GROUP BY,HAVING或DISTINCT子句的影响。

• TOP is not used anywhere in the select_statement of the view together with the WITH CHECK OPTION clause.

TOP不会在视图的select_statement中的任何位置与WITH CHECK OPTION子句一起使用。

Here not only does your view uses the UNION statement, the AutoProcess field you are trying to update is actually the result of a CASE statement that uses two fields. It makes no sense to try and update that.

这里不仅您的视图使用UNION语句,您尝试更新的AutoProcess字段实际上是使用两个字段的CASE语句的结果。尝试更新它是没有意义的。

I would recommend that you use stored proc to perform writing operations. Or, as Damien suggest, you could use an INSTEAD OF trigger on the view too.

我建议您使用存储过程执行写入操作。或者,正如Damien建议的那样,您也可以在视图上使用INSTEAD OF触发器。

#2

You have to create a TRIGGER and manually apply the changes from the inserted and deleted pseudo-tables against the base tables yourself.

您必须创建TRIGGER并自行将插入和删除的伪表中的更改应用于基表。

#3

There is no way for sql server to work backwards from your convert functions to the original fields. You cannot update a view this way.

sql server无法从转换函数向后工作到原始字段。您无法以这种方式更新视图。

If the view contained your jptName and subName fields, you might be able to update just those fields.

如果视图包含您的jptName和subName字段,则可能只能更新这些字段。