In this blog post, let’s learn about the error message “4406 – Update or insert of view or function ‘%.*ls’ failed because it contains a derived or constant field.” in Microsoft SQL Server, the reason why it appears and the solution to fix it.
SQL Server Error Message
4406 – Update or insert of view or function ‘%.*ls’ failed because it contains a derived or constant field.
Reason for the Error
The SQL Server Error Msg 4406 occurs when an attempt is made to update or insert data into a view or function that contains a derived or constant field. A derived or constant field is a field that is not directly stored in the table or view, but is calculated or derived from other fields or constants.
Here are some examples that can cause the error:
Example 1: Updating a computed column Suppose you have a view that contains a computed column that calculates the sum of two columns:
CREATE VIEW View1 AS SELECT Column1, Column2, (Column1 + Column2) AS ComputedColumn FROM Table1
If you attempt to update the ComputedColumn directly, you will receive the error message:
UPDATE View1 SET ComputedColumn = 10 WHERE Column1 = 1 Msg 4406, Level 16, State 1, Line 1 Update or insert of view or function 'View1' failed because it contains a derived or constant field.
Solution
To fix this error, you need to update the underlying columns that are used to calculate the computed column:
UPDATE View1 SET Column1 = 3, Column2 = 7 WHERE Column1 = 1
In summary, the SQL Server Error Msg 4406 can occur when attempting to update or insert data into a view or function that contains a derived or constant field. To fix the error, you need to modify the underlying columns, update the view or function definition, or modify the table to make it updatable.
