Error on update SQL view, please help

[tnguyen315]

I'm using SQL 2008 R2. I'm trying to update the MAX record from another table to the view but got error.

Error "Msg 4406, Level 16, State 1, Line 1
Update or insert of view or function 'dbo.V_Notes_1' failed because it contains a derived or constant field."

My code:
UPDATE dbo.V_Notes_1
SET V_TBVS_NO=(SELECT MAX(TBVS_NO) FROM dbo.ITVS WHERE dbo.V_Notes_1.CUSID = dbo.ITVS.CUSID);


Table ITVS:

CUSID TBVS_NO
1 42
1 43
2 44
2 45
3 49



Then should update to the sql view:
CUSID TBVS_NO
1 43
2 45
3 49


Please help, I'm very appreciated.
Again thanks
Tiffanie

 

[gmmastros]

Not all views are updateable. It depends on the view definition. Instead of updating the view, you could always update the base table that the view uses. To see what I mean, run this:

sp_helptext 'dbo.V_Notes_1'

This should show you the query that the view is based on.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom