insert and update data in two different tables

[szlamany]

What is the database - ACCESS or MS SQL or MySQL - I really need to know that also - thanks.

[escolarm]

What is the database - ACCESS or MS SQL or MySQL - I really need to know that also - thanks.

It is a Microsoft SQL Server 2000 database.

[szlamany]

Well I've always wanted to try this - seeing if a VIEW can be updated.

So I did this in QUERY ANALYZER - but it doesn't work.

--Drop Table Test1 Drop Table Test2
Create Table Test1 (keycol int, datacol1 varchar(1))
Insert into Test1 values (1,'A')
Create Table Test2 (keycol int, datacol2 varchar(1))
Insert into Test2 values (1,'Z')
Select * from Test1
Select * from Test2
Go
Create View View1 as Select T1.KeyCol,T1.DataCol1,T2.DataCol2 From Test1 T1 Left Join Test2 T2 on T2.KeyCol=T1.KeyCol
Go
Select * from View1
Update View1 Set DataCol1='B',DataCol2='B' Where KeyCol=1
Select * from View1

Gets this error:

View or function 'View1' is not updatable because the modification affects multiple base tables.

That's a shame.

Since you are using MS SQL Server - have you considered using a STORED PROCEDURE to do the update - so you can encapsulate both table UPDATES in a single TRANSACTION?

[escolarm]

Well I've always wanted to try this - seeing if a VIEW can be updated.

So I did this in QUERY ANALYZER - but it doesn't work.

--Drop Table Test1 Drop Table Test2
Create Table Test1 (keycol int, datacol1 varchar(1))
Insert into Test1 values (1,'A')
Create Table Test2 (keycol int, datacol2 varchar(1))
Insert into Test2 values (1,'Z')
Select * from Test1
Select * from Test2
Go
Create View View1 as Select T1.KeyCol,T1.DataCol1,T2.DataCol2 From Test1 T1 Left Join Test2 T2 on T2.KeyCol=T1.KeyCol
Go
Select * from View1
Update View1 Set DataCol1='B',DataCol2='B' Where KeyCol=1
Select * from View1

Gets this error:

View or function 'View1' is not updatable because the modification affects multiple base tables.

That's a shame.

Since you are using MS SQL Server - have you considered using a STORED PROCEDURE to do the update - so you can encapsulate both table UPDATES in a single TRANSACTION?

Yes I used a stored procedure but it produces the same error.

[szlamany]

Post the code for the SPROC - I should be able to correct it for you...