如何在sql server 2008中更新多个表？ [英] How to update multiple tables in 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屋！