Updating two tables in a single query in sql

I tried to update the tables in that view and I get the error. Seeing all the records in the view where unit 71054 I tried the update without the where cause all the records was that unitupdate tmp_mfg_unit_2set t_cmnf='OTH'and still the same error UNION ALL view 'tmp_mfg_unit_2' is not updatable because the definition contains a disallowed construct. I deleted the declare and added '71054' to parts of the the view works perfectly.I tried to update the tables in that view and I get the error. All the records in the view was of unit 71054 I tried the update without the where clause.update tmp_mfg_unit_2set t_cmnf='OTH'and still the same error UNION ALL view 'tmp_mfg_unit_2' is not updatable because the definition contains a disallowed construct.

Schemabinding is not required to make a view updatable.

Gail Shaw Microsoft Certified Master: SQL Server, MVP, M.

Sc (Comp Sci)SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability We walk in the dark places no others will enter We stand on the bridge and no one may pass The only way to update multiple tables in a single statement is when you create a view over these tables and use the option WITH SCHEMABINDING.

Updatable Views You can modify the data of an underlying base table through a view, as long as the following conditions are true: Any modifications, including UPDATE, INSERT, and DELETE statements, must reference columns from only one base table. This is what I had in mind and though I learned from the theory.

Hi, I have two tables table1 and table2 and having a common column "col1"When i ran the following query UPDATE table1, table2 SET col1=FALSE WHERE id = 1;getting the following error Error Code: 1052Column 'col1' in field list is ambiguousid column exist in both the tables and need to update both the tables to false where the id is equivalent to 1.could you please help me on how to achieve this in single query? with a UPDATE statement you can only update one single table at the time.

You could filter the rows from that table using multiple tables with a JOIN statement in the FROM part.

We've got lots of great SQL Server experts to answer whatever question you can come up with. Hello All, I want to update multiple tables using single query and fields name are same of tables. USE Northwind GO CREATE TABLE my Table99 (Col1 int PRIMARY KEY CHECK (Col1 BETWEEN 1 AND 10), Col2 varchar(50)) CREATE TABLE my Table98 (Col1 int PRIMARY KEY CHECK (Col1 BETWEEN 11 AND 20), Col2 varchar(50)) GO INSERT INTO my Table99(Col1, Col2) SELECT 1, 'x' UNION ALL SELECT 2, 'y' UNION ALL SELECT 3, 'z' INSERT INTO my Table98(Col1, Col2) SELECT 11, 'x' UNION ALL SELECT 12, 'y' UNION ALL SELECT 13, 'z' GO CREATE VIEW my View99 AS SELECT Col1, Col2 FROM my Table99 UNION ALL SELECT Col1, Col2 FROM my Table98 GO SELECT * FROM my View99 UPDATE my View99 SET Col2 = 'x002548' WHERE Col2 = 'z' SELECT * FROM my View99 /* DROP VIEW my View99 DROP TABLE my Table99, my Table98 */ Found this trivia on the net a while ago DECLARE @Table A TABLE (i INT)DECLARE @Table B TABLE (i INT)SELECT * FROM @Table ASELECT * FROM @Table BINSERT @Table AOUTPUT inserted.i INTO @Table BSELECT 1 UNION ALLSELECT 2 UNION ALLSELECT 3SELECT * FROM @Table ASELECT * FROM @Table BJim create view temp_mfg_unit_view as Declare @unit varchar(16) set @unit='71054' select t_cmnf, t_clot from ttscfg200100 --objects where [email protected] union all select t_cmnf, t_clot from terext401100 --rental contract lines where [email protected] I didn't put all the tables in the code. Post ID=2669779&Site ID=1Originally posted by cardgunner I was afraid of that.

We've restricted the ability to create new threads on these forums. In any case I did cahnge @unit with '71054' and scratched the declare. Worked perfectly Tried to update the t_cmnf and got an error. One way to deal with this is to create an INSTEAD OF UPDATE trigger on view which updates the base tables instead of updating the view directly. Post.aspx?

Card Gunner I didn't put all the tables in the code. I'll have to see if '71054' can be called as a parameter in VS 2003.

Tags: , ,