Updating a view in sqlserver

[uniquegodwin]

Hello everyone,
I'm using sqlserver 2005 with SQL management studio.I have a view and it has a problem.
The problem is that...SQL management studio automatically generated some sql script for me to update the view.But,it doesn't work.HELP ME please! This is the script it gave me...
-------------
UPDATE [skips].[dbo].[Schedule]
SET [ContractId] = <ContractId, int,>
,[DriverId] = <DriverId, int,>
,[TruckId] = <TruckId, int,>
,[Completed] = <Completed, bit,>
,[Day] = <Day, varchar,>
WHERE <Search Conditions,,>
--------------
When I try to execute it...I get this error ,
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near '<'.

How do I make this work and update the view?
Thanks so much in advance

[corwin_ranger]

The issue is that SQL Management Studio created some generic script for you to modify. In this case, it's actually an SQL update statement to update data using that view. It is NOT code to update the way the view itself works, if it were, you'd be looking at an ALTER VIEW statement.

So, in short, if you want to change the way the view works, you need an ALTER VIEW statement. If you want to update the underlying data using the view, then all you have to do is replace all the items bracketed by <> with appropriate values and then the code you have will run.

Steve

[uniquegodwin]

Hello Corwin,
Thanks so much for the reply...
I replaced the stuff "<>" with values...
Now it gives me this error..
-----
View or function 'skips.dbo.Schedule' is not updatable because the modification affects multiple base tables
-----
Please help...
Thanks so much

[corwin_ranger]

That means that the view looks at a number of tables and the joins are such that you can't update the data in those tables using this view.

What exactly are you trying to accomplish?

[uniquegodwin]

Hi,
Im trying to update the values of the view in their respective base tables.The data comes from 4 different tables. :-)
Thanks

[corwin_ranger]

Not such an easy thing to do. If this is just a one-off sort of thing that you're not likely to do again, I'd say just carefully update the tables individually.

If it's something that's going to happen regularly, how will it be updated day-to-day? Via an application of some kind? One that you're writing. Talk to me about the process and what you're trying to do overall.

[uniquegodwin]

yep,It's a C# application. The application stores data into various tables...and retrives.That works perfectly.
Now this is like a daily schedule that should be viewable to the user. But,Incase he wants to update these values,they want that facility too...
I don't mind if there's an alternative to achieve this

[corwin_ranger]

I would say that the best alternative would be to write a parameter based Stored Procedure to handle the update directly to each table as required.

Do any of these tables represent a parent/child, one/many relationship (i.e. one entry in the parent table and potentially more than one entry in the child table)? If so, then you'll nedd to wrap the SQL up in a transaction to make sure that all changes happen.

Steve

[uniquegodwin]

Hello Steve,
Thanks so much...
The tables relate between themselves...It's about 8 tables totally with various datas.
Now,I'm showing only 4 columns from different tables on this view.

I think the solution would be to use a trigger that would update the respective columnns when an update action is performed on the view.
But,the problem is that I am struggling in creating a trigger capable of handling that

Please help me with a stored procedure or trigger if you can...
Thanks so much..

[corwin_ranger]

I'd need more detail about the table structures to help you with this. A trigger strikes me as a bit overkill and won't really solve your problem. To really help you further I'll need the following info:

The structure of the tables involved.
What sort of relationships exist between the tables (1 - many, 1 - 1).
The structure of the view.
What are you updating from (an application, just a SQL query, etc...)?
What fields are you trying to update.