如何在sql server 2008中更新多个表? [英] How to update multiple tables in sql server 2008 ?
本文介绍了如何在sql server 2008中更新多个表?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
大家好,
我想在一个UPDATE查询中更新多个表中的多个列...
只是想要如下查询...
更新表1,表2
SET Table1.Column1 = ' one'
, Table2.Column2 = ' two'
FROM 表1 T1,表2 T2
WHERE T1.id = T2.id
和 T1.id = ' id1'
Sql Server 2008是否提供了这样做的机制?
如果是Sql Server 2008提供了这样好的方法,请与我分享一些链接!
谢谢!
解决方案
任何修改,包括更新, INSERT ,和 DELETE 语句必须仅从一个表引用列。
我认为最佳的方法:使用事务,可能将业务逻辑包装到存储过程中。代码框架可能如下所示:
开始 tran
< TSQL语句 table 1>
如果 @@ error != 0 goto 错误
< TSQL语句 table 2>
如果 @@ error != 0 goto 错误
commit tran
goto ok
错误:
< error_handler>
rollback tran
ok:
尝试在更新级联上使用外键。
查看id'或其他任何方式可以应用key-外键约束
Hi All,
I want to update multiple columns from multiple tables in a single UPDATE Query...
Just want to do like below query...
UPDATE Table1, Table2
SET Table1.Column1 = 'one'
,Table2.Column2 = 'two'
FROM Table1 T1, Table2 T2
WHERE T1.id = T2.id
and T1.id = 'id1'
Does Sql Server 2008 provide any mechanism to do so?
If Sql Server 2008 provide such nice approach, please share some links with me!
Thanks!
解决方案
Well, in short - not possible.The same restriction for updateable views:Any modifications, including UPDATE, INSERT, and DELETE statements, must reference columns from only one base table.
Approach that I believe is optimal: use transactions, possibly wrap your business logic into stored procedures. Code skeleton might look like below:
begin tran <TSQL statement for table 1> if @@error != 0 goto error <TSQL statement for table 2> if @@error != 0 goto error commit tran goto ok error: <error_handler> rollback tran ok:
try using foreign key "on update cascade".
see if id''s or by any other means primary key- foreign key constraint can be applied
这篇关于如何在sql server 2008中更新多个表?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文