Updating multiple tables sql server
Can you do this type of statement when creating a view? Card Gunner Originally posted by jimf 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 BOriginally posted by cardgunner I was afraid of that.
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. UNION ALL view 'tmp_mfg_unit_2' is not updatable because the definition contains a disallowed ideas?
id Data, -- PK of main table tab Data fi SL, fi Model, fi Claim Status -- FK to dim Claim Status FROM tab Data AS d INNER JOIN loc SL AS sl ON SL = SL INNER JOIN loc GSP AS gsp ON GSP = GSP INNER JOIN loc Country AS c ON Country = Country INNER JOIN loc Market Unit AS mu ON Market Unit = Market Unit INNER JOIN mod Model AS m ON Model = Model INNER JOIN dim Claim Status AS s ON Claim Status = Claim Status INNER JOIN tdef Product Type ON Product Type = tdef Product Product Type LEFT OUTER JOIN tdef Service Level ON d.fimax Service Level = tdef Service Service Level LEFT OUTER JOIN tdef Action Code AS ac ON d.fimax Action Code = Action Code UPDATE tab Data SET fi Claim Status = (SELECT id Claim Status FROM dim Claim Status WHERE Claim Status Name = 'Awaiting auth.') WHERE fi Claim Status=(SELECT id Claim Status FROM dim Claim Status WHERE Claim Status Name = 'Approved') updatable in practice, due to limitations of the query processor's reasoning.
It is possible, however, to manually create an updategram that will update multiple tables.
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.
Card Gunner create view tmp_mfg_unit_2 as select t_cmnf, t_clot from ttscfg200120 --objects where t_clot='23332' union all select t_cmnf, t_clot from terext401120 --rental contract lines where t_clot='23332' update tmp_mfg_unit_2 set t_cmnf='OTH' Server: Msg 4416, Level 16, State 5, Line 1 UNION ALL view 'tmp_mfg_unit_2' is not updatable because the definition contains a disallowed construct.
you're not breaking the rules for updateable views, then you should be okay. So not using a view for updates seems to be the safest approach.
Typically, it does a good job with this, so the update view plan may look very similar to a plan for a simple update to the single affected base table. Then read this blog I wrote recently to see how it could be affected. But actually it didn't return records because the referenced table has changed by the update.Microsoft is conducting an online survey to understand your opinion of the Msdn Web site.