Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
juvethski
Starting Member
46 Posts |
 Posted - 2005-10-26 : 17:06:37
|
| hi,i'm looking for suggestions on how to 'update' data on a 'view'. from the error message i'm getting, its saying that updating columns on a view can only be done if the TOP keyword is specified. here's an example:create view v1 asselect a, b, cfrom x,ywhere a = dupdate v1set a = 'a'the update statement will fail because of the following:Update or insert of view or function 'v1_matched' failed because it contains a derived or constant field.orit was derived from multiple views or tables.thanks!~~~~~~~~~~~~~~~~how's your data? |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2005-10-26 : 17:39:34
|
| It would be easier to help you if you posted the DDL code for the underlying tables in the view.Have you thought about just updating the underlying tables directly?CODO ERGO SUM |
 |
|
|
juvethski
Starting Member
46 Posts |
Posted - 2005-10-26 : 18:40:27
|
| can't update the underlying tables because the values is dependent on the result. and i'm really trying to avoid created tmp tables.thanks!~~~~~~~~~~~~~~~~how's your data? |
|
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2005-10-26 : 19:40:13
|
| I suspect that your update is failing because in your query field [a] is required to have a constant value [d], and you are trying to modify [a] in a manner which makes it no longer satisfy the criteria of the query.If this is a regular process, part of OLTP for instance, then you may be able to put an INSTEAD OF trigger on the view to handle updates.If this is part of a stored proc or is a one-time or infrequent task, then you might take Michael's advice and update the underlying table directly by LINKING IT TO THE RESULTS OF YOUR VIEW. |
|
|
|
steamngn
Constraint Violating Yak Guru
306 Posts |
Posted - 2005-10-26 : 19:49:50
|
| juvethski:See BOL: search for "views-SQL Server" then the subsection "modifying data"...AndyThere's never enough time to type code right, but always enough time for a hotfix... |
|
|
|
|
|
|
updating 'view'