Member Avatar for bhagawatshinde

Hi guys can we update data (Edit) in view table in sql server? If Yes How ,if No Why?

Thanks in advance......

  • 2 Contributors
  • 3 Replies
  • 287 Views
  • 1 Week Discussion Span
  • Latest Post Latest Post by thines01

Dani AI

Generated

Short answer for (and thanks to for the pointer): yes — SQL Server allows INSERT/UPDATE/DELETE through a view, but only when the view is “updatable” (the engine can unambiguously map the change to the underlying table). Complex views (aggregates, DISTINCT, GROUP BY, set ops, computed columns, etc.) are generally read‑only unless additional work is done. (learn.microsoft.com)

Key restrictions to check before trying to update a view:

  • The modification must map to columns from a single base table.
  • The modified columns must be direct column references (no aggregates, no derived/computed expressions, no GROUP BY/HAVING/DISTINCT).
  • TOP with WITH CHECK OPTION is restricted.
  • If the view joins multiple tables you can typically update only columns that belong to one underlying table; you cannot delete through a multi‑table view.
    These rules are documented in the CREATE VIEW / Modify Data Through a View guidance. (learn.microsoft.com)

If the view is not directly updatable, use an INSTEAD OF trigger on the view to implement the required DML logic (the trigger runs instead of the attempted INSERT/UPDATE/DELETE and can route changes to one or more base tables). Example pattern (trimmed):

CREATE TRIGGER tr_vwOrders_IOU
ON dbo.vwOrderDetails
INSTEAD OF UPDATE
AS
BEGIN
  UPDATE od
  SET od.UnitPrice = i.UnitPrice, od.Quantity = i.Quantity
  FROM dbo.OrderDetails od
  JOIN inserted i ON od.OrderID = i.OrderID AND od.ProductID = i.ProductID;
END;

INSTEAD OF triggers are the supported way to make complex views accept DML. (learn.microsoft.com)

Quick troubleshooting checklist when an UPDATE fails:

  • Try the same UPDATE directly against the base table to reveal constraint/permission errors.
  • Inspect the view definition for aggregates, derived columns, TOP/DISTINCT, or multiple base tables.
  • Check WITH CHECK OPTION (it prevents making a row that would no longer appear in the view).
  • Verify permissions on the underlying table and any triggers.
    For step‑by‑step examples and exact rules, see Microsoft’s CREATE VIEW and Modify Data Through a View docs. (learn.microsoft.com)

Recommended Answers

All 3 Replies

Member Avatar for bhagawatshinde

Hi thines01, Trouble to open link

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.