一次更新2个或更多表中的数据 [英] Update data in 2 or more table in one time

问题描述

一次更新2个或更多表中的数据

Update data in 2 or more table in one time

推荐答案

假设这两个表以某种方式相关（或者没有太多意义）最好的方法是编写一个存储过程，它接受所有数据作为参数，启动事务，更新第一个表，传输任何身份信息并更新第二个表，然后提交事务。这样，如果任一表更新失败，整个事务可以回滚，并且您的实时数据库在任何时候都不包含不一致或孤立的信息..



您可以在SP之外执行此操作，但总是使用SP进行此类更新和插入会更安全。

Assuming that the two tables are related in some way (or there wouldn't be much point) the best way is to write a stored procedure which accepts all the data as parameters, starts a transaction, updates the first table, transfers any identity information and updates the second table, then commits the transaction. That way, if there is a failure in either table update, the whole transaction can be rolled back and at no time does your "live" DB contain inconsistent or "orphaned" information..

You can do this outside an SP, but it's just a little safer to always use an SP for such updates and inserts.

简单答案是不可能（如我知道的。）你必须编写2个UPDATE语句。

我不确定其他数据库，但在SQL Server中，它不是那么容易，不推荐，除非你有足够的理由这样做，那就是使用VIEW。但说真的，我从未见过有人采用这种方法。你可以通过GOOGLE。



花一些时间查看之前关于此的讨论会很有用 -

如何在SQL Server 2005中的一个语句中更新两个表？ [ ^ ]

使用INNER JOIN更新SQL Server中的多个表格[ ^ ]

我们如何使用单个sql查询更新两个不同的表 [ ^ ]



希望，它有帮助：）

Simple answer is "Not possible" (as far I know). You have to write 2 UPDATE statements.
I am not sure about other DBs but in SQL Server, it is not so easy and not recommended unless you have sufficient cause to do this, that's using VIEW. But seriously, I have never seen anybody taking this approach. You can GOOGLE for that.

It'll be useful to spend sometime looking at previous discussions on this-
How to update two tables in one statement in SQL Server 2005?[^]
Update multiple tables in SQL Server using INNER JOIN[^]
How can we update two different tables with single sql query[^]

Hope, it helps :)

这篇关于一次更新2个或更多表中的数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！