How to fix Error 4421 on Windows Server 2025 / Windows 11

by

Troubleshooting Guide: SQL Server Error 4421

Why This Error Happens

This error occurs when an UPDATE, DELETE, or INSERT statement targets a derived table (a subquery or view used in the FROM clause) that contains a computed column, a constant value, or an aggregate expression. SQL Server’s query engine cannot map the modification back to a single underlying base table column because the derived column has no direct one-to-one correspondence with physical storage. This is a design limitation of updatable views/derived tables, not a corruption or permissions issue, and typically surfaces after a schema change, a query rewrite, or an ORM-generated query that introduces calculated fields into what was previously a simple pass-through view.

Step-by-Step Resolution Guide

Step 1: Identify the Offending Column in the Derived Table

  • Open the query or stored procedure referenced in the error message and locate the derived table (subquery/CTE/view) named in the %.*ls placeholder.
  • Check for columns using expressions such as SUM(), COUNT(), CASE WHEN, string concatenation (+), or literal constants (e.g., 0 AS Flag, 'Active' AS Status).
  • Run the inner query standalone in SSMS to confirm which columns are computed versus directly mapped from base table columns.

Step 2: Rewrite the Query to Separate Computed Logic from the Update Target

  • Modify the UPDATE/DELETE statement to reference the base table directly instead of the derived table, using a JOIN to bring in any filtering logic.
  • Example pattern:
  UPDATE t
  SET t.ColumnName = @NewValue
  FROM BaseTable t
  INNER JOIN (SELECT Id FROM BaseTable WHERE SomeCondition = 1) AS Filtered
      ON t.Id = Filtered.Id;
  • Ensure the derived table is used only for filtering/joining purposes, not as the direct target of the DML operation.

Step 3: Fix Underlying Views if the Derived Table Is a View

  • If the derived table is actually a persisted view, open it via sp_helptext 'ViewName' in SSMS to inspect its definition.
  • Remove or isolate any computed/constant columns from the view, or create a second “updatable” view containing only pass-through columns from a single base table.
  • Re-deploy the view using CREATE OR ALTER VIEW and confirm WITH SCHEMABINDING is not masking an underlying computed column.

Step 4: Validate Application or ORM-Generated Queries

  • If using Entity Framework, Dapper, or another ORM, inspect the generated SQL (via SQL Server Profiler or Extended Events) to see if the ORM is auto-injecting calculated columns into the derived table.
  • Adjust the ORM mapping or raw SQL query to exclude computed properties from the update context.

Alternative or Verification Steps

  • Re-run the original UPDATE/DELETE statement after the rewrite; if Error 4421 no longer appears and the row count matches expectations, the fix is confirmed.
  • As an alternative, use an INSTEAD OF trigger on the view to manually handle the update logic, redirecting writes to the correct base table columns while bypassing the derived/constant column restriction entirely.