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

How to update two tables in one statement in SQL Server 2005?

提问by Jango

I want to update two tables in one go. How do I do that in SQL Server 2005?

我想一次性更新两个表。我如何在 SQL Server 2005 中做到这一点？

UPDATE 
  Table1, 
  Table2
SET 
  Table1.LastName='DR. XXXXXX', 
  Table2.WAprrs='start,stop'
FROM 
  Table1 T1, 
  Table2 T2
WHERE 
  T1.id = T2.id
AND 
  T1.id = '010008'

回答by LBushkin

You can't update multiple tables in one statement, however, you can use a transaction to make sure that two UPDATEstatements are treated atomically. You can also batch them to avoid a round trip.

您不能在一个语句中更新多个表，但是，您可以使用事务来确保UPDATE以原子方式处理两个语句。您还可以批量处理它们以避免往返。

BEGIN TRANSACTION;

UPDATE Table1
  SET Table1.LastName = 'DR. XXXXXX' 
FROM Table1 T1, Table2 T2
WHERE T1.id = T2.id
and T1.id = '011008';

UPDATE Table2
SET Table2.WAprrs = 'start,stop'
FROM Table1 T1, Table2 T2
WHERE T1.id = T2.id
and T1.id = '011008';

COMMIT;

回答by Remus Rusanu

You can't update two tables at once, but you can link an update into an insert using OUTPUT INTO, and you can use this output as a join for the second update:

您不能一次更新两个表，但可以使用 将更新链接到插入中OUTPUT INTO，并且可以将此输出用作第二次更新的联接：

DECLARE @ids TABLE (id int);
BEGIN TRANSACTION

UPDATE Table1 
SET Table1.LastName = 'DR. XXXXXX'  
OUTPUT INSERTED.id INTO @ids
WHERE Table1.field = '010008';

UPDATE Table2 
SET Table2.WAprrs = 'start,stop' 
FROM Table2 
JOIN @ids i on i.id = Table2.id;

COMMIT;

I changed your example WHEREcondition to be some other field than id. If it's idthe you don't need this fancy OUTPUT, you can just UPDATEthe second table for the same id='010008'.

我将您的示例WHERE条件更改为id. 如果id是你不需要这个花哨的OUTPUT，你可以只UPDATE用第二张桌子id='010008'。

回答by Charles Bretana

Sorry, afaik, you cannot do that. To update attributes in two different tables, you will need to execute two separate statements. But they can be in a batch ( a set of SQL sent to the server in one round trip)

对不起，afaik，你不能那样做。要更新两个不同表中的属性，您需要执行两个单独的语句。但它们可以是成批的（一组 SQL 一次发送到服务器）

回答by jveazey

The short answer to that is no. While you can enter multiple tables in the fromclause of an update statement, you can only specify a single table after the updatekeyword. Even if you do write a "updatable" view (which is simply a view that follows certain restrictions), updates like this will fail. Here are the relevant clips from the MSDN documentation (emphasis is mine).

对此的简短回答是否定的。虽然您可以在from更新语句的子句中输入多个表，但您只能在update关键字后指定一个表。即使您确实编写了一个“可更新”视图（它只是一个遵循某些限制的视图），这样的更新也会失败。以下是 MSDN 文档中的相关剪辑（重点是我的）。

UPDATE (Transact-SQL)

更新 (Transact-SQL)

The view referenced by table_or_view_name must be updatable and reference exactly one base tablein the FROM clause of the view. For more information about updatable views, see CREATE VIEW (Transact-SQL).

table_or_view_name 引用的视图必须是可更新的，并且在视图的 FROM 子句中引用了一个基表。有关可更新视图的详细信息，请参阅 CREATE VIEW (Transact-SQL)。

CREATE VIEW (Transact-SQL)

创建视图 (Transact-SQL)

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.
• The columns being modified in the view must directly reference the underlying data in the table columns. The columns cannot be derived in any other way, such as through the following:
  • An aggregate function: AVG, COUNT, SUM, MIN, MAX, GROUPING, STDEV, STDEVP, VAR, and VARP.
  • A computation. The column cannot be computed from an expression that uses other columns. Columns that are formed by using the set operators UNION, UNION ALL, CROSSJOIN, EXCEPT, and INTERSECT amount to a computation and are also not updatable.
• The columns being modified are not affected by GROUP BY, HAVING, or DISTINCT clauses.
• TOP is not used anywhere in the select_statement of the view together with the WITH CHECK OPTION clause.

您可以通过视图修改底层基表的数据，只要满足以下条件即可：

• 任何修改，包括 UPDATE、INSERT 和 DELETE 语句，都必须仅引用一个基表中的列。
• 视图中被修改的列必须直接引用表列中的基础数据。列不能以任何其他方式派生，例如通过以下方式：
  • 聚合函数：AVG、COUNT、SUM、MIN、MAX、GROUPING、STDEV、STDEVP、VAR 和 VARP。
  • 一个计算。不能从使用其他列的表达式计算该列。使用集合运算符 UNION、UNION ALL、CROSSJOIN、EXCEPT 和 INTERSECT 形成的列相当于计算并且不可更新。
• 被修改的列不受 GROUP BY、HAVING 或 DISTINCT 子句的影响。
• TOP 未在视图的 select_statement 中与 WITH CHECK OPTION 子句一起使用。

In all honesty, though, you should consider using two different SQL statements within a transaction as per LBushkin's example.

不过，老实说，您应该考虑按照 LBushkin 的示例在事务中使用两个不同的 SQL 语句。

UPDATE:My original assertion that you could update multiple tables in an updatable view was wrong. On SQL Server 2005 & 2012, it will generate the following error. I have corrected my answer to reflect this.

更新：我最初关于您可以在可更新视图中更新多个表的断言是错误的。在 SQL Server 2005 和 2012 上，它将生成以下错误。我已经更正了我的答案以反映这一点。

Msg 4405, Level 16, State 1, Line 1

View or function 'updatable_view' is not updatable because the modification affects multiple base tables.

Msg 4405, Level 16, State 1, Line 1

View or function 'updatable_view' is not updatable because the modification affects multiple base tables.

回答by user3662407

This works for MySQL and is really just an implicit transaction but it should go something like this:

这适用于 MySQL，实际上只是一个隐式事务，但它应该是这样的：

UPDATE Table1 t1, Table2 t2 SET 
t2.field = t2.field+2,
t1.field = t1.field+2

WHERE t1.id = t2.foreign_id and t2.id = '123414'

if you are doing updates to multi tables that require multi statements… which is likely possible if you update one, then another based on other conditions… you should use a transaction.?

如果您正在对需要多条语句的多表进行更新……如果您更新一个，然后根据其他条件更新另一个，这可能是可能的……您应该使用事务。？

回答by Mick

You should place two update statements inside a transaction

您应该在事务中放置两个更新语句

回答by Sandip - Full Stack Developer

You can write updatestatement for one tableand then a triggeron first tableupdate, which update second table

您可以为一个表编写更新语句，然后在第一个表更新时编写触发器，从而更新第二个表

回答by Ricardo Roa

From my perspective you can do this, its one to one update of two tables in SQL SERVER:

从我的角度来看，您可以这样做，它是 SQL SERVER 中两个表的一对一更新：

 BEGIN TRANSACTION

      DECLARE @CNSREQ VARCHAR(30)
      DECLARE @ID INT
      DECLARE @CNSRQDT VARCHAR(30)
      DECLARE @ID2 INT

      DECLARE @IDCNSREQ INT
      DECLARE @FINALCNSREQ VARCHAR(30)
      DECLARE @FINALCNSRQDT VARCHAR(30)
      DECLARE @IDCNSRQDT INT


      SET @CNSREQ=(SELECT MIN(REQUISICIONESDT.CNSREQ) FROM REQUISICIONESDT
          INNER JOIN 
              REQUISICIONES
                ON REQUISICIONESDT.CNSRQDT = REQUISICIONES.CNSRQDT AND REQUISICIONES.IDRQDT = REQUISICIONESDT.ID
           AND REQUISICIONES.CNSREQ = REQUISICIONESDT.CNSREQ AND REQUISICIONESDT.IDREQ = REQUISICIONES.ID
        WHERE REQUISICIONES.CNSRQDT = REQUISICIONES.CNSRQDT AND REQUISICIONES.IDRQDT = REQUISICIONESDT.ID)

      SELECT REQUISICIONES.CNSREQ, REQUISICIONES.ID, REQUISICIONES.CNSRQDT FROM REQUISICIONES
       INNER JOIN 
          REQUISICIONESDT
              ON REQUISICIONESDT.CNSRQDT = REQUISICIONES.CNSRQDT AND REQUISICIONES.IDRQDT = REQUISICIONESDT.ID
               AND REQUISICIONES.CNSREQ = REQUISICIONESDT.CNSREQ AND REQUISICIONESDT.IDREQ = REQUISICIONES.ID
        WHERE REQUISICIONES.CNSRQDT = REQUISICIONES.CNSRQDT AND REQUISICIONES.IDRQDT = REQUISICIONESDT.ID
    AND REQUISICIONES.CNSREQ = @CNSREQ

        UPDATE REQUISICIONESDT SET  REQUISICIONESDT.CNSREQ=NULL, REQUISICIONESDT.IDREQ=NULL
          FROM REQUISICIONES INNER JOIN REQUISICIONESDT
             ON REQUISICIONESDT.CNSRQDT = REQUISICIONES.CNSRQDT AND REQUISICIONES.IDRQDT = REQUISICIONESDT.ID
       WHERE REQUISICIONES.CNSRQDT = REQUISICIONES.CNSRQDT AND REQUISICIONES.IDRQDT = REQUISICIONESDT.ID
      AND REQUISICIONES.CNSREQ = @CNSREQ

        UPDATE REQUISICIONES SET REQUISICIONES.CNSRQDT=NULL, REQUISICIONES.IDRQDT=NULL
          FROM REQUISICIONES INNER JOIN REQUISICIONESDT
          ON REQUISICIONESDT.CNSRQDT = REQUISICIONES.CNSRQDT AND REQUISICIONES.IDRQDT = REQUISICIONESDT.ID
       WHERE REQUISICIONES.CNSRQDT = REQUISICIONES.CNSRQDT AND REQUISICIONES.IDRQDT = REQUISICIONESDT.ID
     AND REQUISICIONES.CNSREQ = @CNSREQ

       SET @ID2=(SELECT MIN(REQUISICIONESDT.ID) FROM REQUISICIONESDT
        WHERE ISNULL(REQUISICIONESDT.IDREQ,0)<>0)
     DELETE FROM REQUISICIONESDT WHERE REQUISICIONESDT.ID=@ID2


      SET @IDCNSREQ=(SELECT MIN (REQUISICIONES.ID)FROM REQUISICIONES
          INNER JOIN REQUISICIONESDT ON
        REQUISICIONESDT.CEDULA = REQUISICIONES.CEDULA AND REQUISICIONES.FECHA_SOLICITUD = REQUISICIONESDT.FECHA_SOLICITUD
         WHERE REQUISICIONES.CNSRQDT IS NULL AND REQUISICIONES.IDRQDT IS NULL)

        SET @FINALCNSREQ=(SELECT MIN (REQUISICIONES.CNSREQ)FROM REQUISICIONES
            INNER JOIN REQUISICIONESDT ON
        REQUISICIONESDT.CEDULA = REQUISICIONES.CEDULA AND REQUISICIONES.FECHA_SOLICITUD = REQUISICIONESDT.FECHA_SOLICITUD
         WHERE REQUISICIONES.CNSRQDT IS NULL AND REQUISICIONES.IDRQDT IS NULL)

         SET @FINALCNSRQDT=(SELECT MIN(REQUISICIONESDT.CNSRQDT) FROM REQUISICIONES
           INNER JOIN REQUISICIONESDT ON
          REQUISICIONESDT.CEDULA = REQUISICIONES.CEDULA AND REQUISICIONES.FECHA_SOLICITUD = REQUISICIONESDT.FECHA_SOLICITUD
           WHERE REQUISICIONES.CNSRQDT IS NULL AND REQUISICIONES.IDRQDT IS NULL)

          SET @IDCNSRQDT=(SELECT MIN (REQUISICIONESDT.ID)FROM REQUISICIONES
           INNER JOIN REQUISICIONESDT ON
         REQUISICIONESDT.CEDULA = REQUISICIONES.CEDULA AND REQUISICIONES.FECHA_SOLICITUD = REQUISICIONESDT.FECHA_SOLICITUD  
         WHERE REQUISICIONES.CNSRQDT IS NULL AND REQUISICIONES.IDRQDT IS NULL)

           UPDATE REQUISICIONES SET REQUISICIONES.CNSRQDT = @FINALCNSRQDT, REQUISICIONES.IDRQDT=@IDCNSRQDT FROM REQUISICIONES
            INNER JOIN REQUISICIONESDT ON
             REQUISICIONESDT.CEDULA = REQUISICIONES.CEDULA AND REQUISICIONES.FECHA_SOLICITUD = REQUISICIONESDT.FECHA_SOLICITUD
            WHERE REQUISICIONESDT.CNSRQDT = @FINALCNSRQDT AND REQUISICIONESDT.ID = @IDCNSRQDT 


ROLLBACK TRANSACTION

回答by Mohit Gupta

It is as simple as this query shown below.

它就像下面显示的这个查询一样简单。

UPDATE 
  Table1 T1 join Table2 T2 on T1.id = T2.id
SET 
  T1.LastName='DR. XXXXXX', 
  T2.WAprrs='start,stop'
WHERE 
  T1.id = '010008'