SQL - SQL Server Forums

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.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

SQL

Author

Topic

sayed
Starting Member

4 Posts

Posted - 2008-04-15 : 03:42:40

A VIEW HAS BEEN DEFINED AS FOLLOWS

CREATE VIEW vwSalesorderDetail
AS
SELECT oh.SalesOrderID,TerritoryId,TotalDue,OrderQty,ProductId
FROM Sales.SalesOrderHeader oh JOIN SalesOrderDetail od
ON oh.SalesOrderID = od.SalesOrderID

THE FOLLOWING UPDATE COMMAND GIVE AN ERROR WHEN YOU UPDATE THE ORDERQTY AND TERRITORY ATTRIBUTES

UPDATE vwSalesOrderDetails
SET OrderQty = 2, territoryID = 4
FROM vwSalesOrderDetail
WHERE SalesOrderID = 43659

WHAT WILL BE ITS SOLUTION

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-15 : 03:51:02

What is the error?
What is the table layout (DDL)?

E 12°55'05.25"
N 56°04'39.16"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-04-15 : 04:23:28

quote:
Originally posted by sayed

A VIEW HAS BEEN DEFINED AS FOLLOWS

CREATE VIEW vwSalesorderDetail
AS
SELECT oh.SalesOrderID,TerritoryId,TotalDue,OrderQty,ProductId
FROM Sales.SalesOrderHeader oh JOIN SalesOrderDetail od
ON oh.SalesOrderID = od.SalesOrderID

THE FOLLOWING UPDATE COMMAND GIVE AN ERROR WHEN YOU UPDATE THE ORDERQTY AND TERRITORY ATTRIBUTES

UPDATE vwSalesOrderDetails
SET OrderQty = 2, territoryID = 4
FROM vwSalesOrderDetail
WHERE SalesOrderID = 43659

WHAT WILL BE ITS SOLUTION

Read about Instead of Trigger in sql server help file

Madhivanan

Failing to plan is Planning to fail

chetanb3
Yak Posting Veteran

52 Posts

Posted - 2008-04-15 : 05:24:33

You can't update view with join on different tables.
You have to insert/updated data in base tables if you wnat view to be populated/updated

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2008-04-15 : 05:29:20

quote:
Originally posted by chetanb3

You can't update view with join on different tables.
You have to insert/updated data in base tables if you wnat view to be populated/updated

Not true.

Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-15 : 05:46:42

Just create an instead of update trigger to update base tables instead.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources