SQL Server更新联接概述

This article explores the SQL Server Update Join statement in T-SQL for SQL Server.

本文探讨了T-SQL for SQL Server中SQL Server Update Join语句。

介绍 (Introduction)

We use the Update statement in SQL Server for updating an existing row in a table. We can update all records or few records based on criteria specified in a where clause. Usually, we update a single table with the SQL Update statement.

我们使用SQL Server中的Update语句来更新表中的现有行。 我们可以根据where子句中指定的条件更新所有记录或少量记录。 通常，我们使用SQL Update语句更新单个表。

In a relational database, it is best practice to use normalization in database design. In database normalization, we use multiple tables and define the relationship between them. We can retrieve records from multiple tables with SQL Joins.

在关系数据库中，最佳实践是在数据库设计中使用规范化。 在数据库规范化中，我们使用多个表并定义它们之间的关系。 我们可以使用SQL Joins从多个表中检索记录。

Now a question arises: Can we update multiple tables using SQL Server Update Join? Let’s explore in this article.

现在出现一个问题：我们可以使用SQL Server Update Join更新多个表吗？ 让我们在本文中进行探索。

Let’s create a customer’s table and insert few records in it.

让我们创建一个客户表并在其中插入一些记录。

准备演示环境 (Prepare environment for demonstration)

Create a Customers table and insert few records in it:

创建一个Customers表，并在其中插入一些记录：

CREATE TABLE [dbo].[Customers]
([id]           [INT] identity(1,1), 
 [CustomerName] [VARCHAR](30) NULL, 
 [OrderCount]   [INT] NULL
)
ON [PRIMARY];
GO
Insert into Customers ([CustomerName],[OrderCount]) values('Raj',NULL),('Kusum',NULL),('Akshita',NULL),('John',NULL),('Dan',NULL)

Create an Orders table and insert few records in it:

创建一个Orders表并在其中插入一些记录：

CREATE TABLE [dbo].[Orders]
([Order_ID]      [INT] IDENTITY(1, 1) NOT NULL, 
 [CustomerID]    [INT] NOT NULL, 
 [OrderQuantity] [INT] NOT NULL, 
 [OrderAmount]   [INT] NOT NULL, 
 CONSTRAINT [PK_Orders] PRIMARY KEY CLUSTERED([Order_ID] ASC)
)
ON [PRIMARY];
GO

This table should have a foreign key constraint on [CustomerID] column of Customers table:

该表应在“客户”表的[CustomerID]列上具有外键约束：

ALTER TABLE [dbo].[Orders]  WITH CHECK ADD  CONSTRAINT [FK_Orders_Customers] FOREIGN KEY([CustomerID])
REFERENCES [dbo].[Customers] ([Customerid])
GO
 
ALTER TABLE [dbo].[Orders] CHECK CONSTRAINT [FK_Orders_Customers]
GO
Insert into [dbo].[Orders] (CustomerID, OrderQuantity,OrderAmount) values(1,100,5000)
Insert into [dbo].[Orders] (CustomerID, OrderQuantity,OrderAmount) values(2,150,6879)
Insert into [dbo].[Orders] (CustomerID, OrderQuantity,OrderAmount) values(3,189,7895)

SQL更新语句 (SQL Update statement)

We have the environment ready for demonstration. Before we dig into multiple table updates using SQL Server Update Join, let’s look at the primary form of SQL Update statement.

我们已经准备好进行演示的环境。 在使用SQL Server Update Join研究多个表更新之前，让我们看一下SQL Update语句的主要形式。

Suppose we want to update [OrderQuantity] and [OrderAmount] column of orders table, we first use a SELECT statement to view the record:

假设我们要更新订单表的[OrderQuantity]和[OrderAmount]列，我们首先使用SELECT语句查看记录：

SELECT *
FROM dbo.orders
WHERE CustomerID = 2;

We can use the following SQL Update statement for updating the Orders table. Here we use SQL Alias for the Orders table. Here we replaced the Select statement with the Update statement without much change in the query:

我们可以使用以下SQL Update语句更新Orders表。 在这里，我们将SQL Alias用于Orders表。 在这里，我们将Update语句替换为Select语句，而无需在查询中进行太多更改：

Update O set [OrderQuantity]=200,[OrderAmount]=7896
FROM dbo.orders O
WHERE CustomerID = 2;

We can execute the Select statement and verify the changes:

我们可以执行Select语句并验证更改：

使用SQL JOINSQL UPDATE语句 (SQL UPDATE statement with SQL JOIN)

Now, let’s use SQL Join for retrieving the record from both of the tables. We use the [CustomerID] column for the Join between both of the tables:

现在，让我们使用SQL Join从两个表中检索记录。 我们将[CustomerID]列用于两个表之间的联接：

SELECT C.CustomerName, 
       C.OrderCount, 
       O.OrderAmount, 
       O.OrderQuantity
FROM Customers C
     JOIN Orders O ON C.Customerid = O.CustomerID;

In the output, we can see only three records. An Inner Join retrieves records that exist in both of the tables. CustomerID 1, 2, 3 exists in both the tables, and it is available in result of the SQL Join:

在输出中，我们只能看到三个记录。 内部联接检索两个表中都存在的记录。 两个表中都存在CustomerID 1、2、3，并且由于SQL Join的缘故而可用：

We can see NULL values in the [OrderCount] column of the Customers table. Suppose we want to update this column with the [OrderQuantity] of the orders table. We can use the following syntax for the SQL Server Update Join statement:

我们可以在“客户”表的[OrderCount]列中看到NULL值。 假设我们要使用订单表的[OrderQuantity]更新此列。 我们可以对SQL Server Update Join语句使用以下语法：

UPDATE 
    Tablealias 
SET 
    A.c1 = B.C1
        ...   
FROM 
    tableA A
    [INNER | LEFT] JOIN tableA B ON join_predicate
WHERE 
    where_predicate;

• Specify a base table in which we want to update records. We can also use SQL Join alias instead of a table name
  指定一个我们要在其中更新记录的基表。 我们还可以使用SQL Join别名代替表名
• Specify the column and value of the column that we want to update. We use the Set statement for specifying the values
  指定我们要更新的列和列的值。 我们使用Set语句指定值
• Use SQL Join operator and specify the table name with join conditions. We can either use an Inner Join or Left Join in this predicate
  使用SQL Join运算符，并指定具有连接条件的表名。 我们可以在此谓词中使用内部联接或左联接
• Add Where clause to update only specific rows. It is an optional argument
  添加Where子句以仅更新特定行。 这是一个可选参数

The following query updates the customer table (Update C statement) with the Set operator (SET C.OrderCount = O.OrderQuantity) using the Join between Customers and Orders table (Customers C JOIN Orders O ON C.Customerid = O.CustomerID):

以下查询使用“客户和订单之间的联接”表（“ Customers C JOIN Orders O ON C.Customerid = O.CustomerID” ），使用Set运算符（ SET C.OrderCount = O.OrderQuantity ）更新客户表（Update C语句）：

UPDATE C
  SET 
      C.OrderCount = O.OrderQuantity
FROM Customers C
     JOIN Orders O ON C.Customerid = O.CustomerID;

It returns the output that three rows are affected:

它返回三行受影响的输出：

Execute the Select join statement and verify the records. We can see it shows similar values in the [OrderCount] and [OrderQuantity] columns:

执行Select join语句并验证记录。 我们可以在[OrderCount]和[OrderQuantity]列中看到类似的值：

Suppose on a new financial year, we archive old customer records and start with the zero in the [OrderQuantity] columns of the orders table. We can execute the following SQL Server Update Join statement, and it updates the [OrderQuantity] columns of orders table:

假设在新的会计年度，我们将旧的客户记录存档，并在订单表的[OrderQuantity]列中以零开始。 我们可以执行以下SQL Server Update Join语句，并更新订单表的[OrderQuantity]列：

UPDATE O
  SET 
      O.OrderQuantity = 0
FROM Customers C
     JOIN Orders O ON C.Customerid = O.CustomerID;

We cannot update multiple tables together using SQL Server Update Join. If we try updating multiple columns belonging to different tables, we get the following error message:

我们不能使用SQL Server Update Join一起更新多个表。 如果我们尝试更新属于不同表的多个列，则会收到以下错误消息：

The multi-part identifier “O.OrderAmount” could not be bound.

无法绑定多部分标识符“ O.OrderAmount”。

UPDATE C
  SET 
      C.OrderCount = 0,
    O.OrderAmount = 0, 
      O.OrderQuantity = 0
FROM Customers C
     JOIN Orders O ON C.Customerid = O.CustomerID;

We can use multiple update statements in this case. For example, the following query updates the [OrderCount] column of the customers table:

在这种情况下，我们可以使用多个更新语句。 例如，以下查询更新customers表的[OrderCount]列：

UPDATE C
  SET 
      C.OrderCount = 0
FROM Customers C
     JOIN Orders O ON C.Customerid = O.CustomerID;

Next Update statements, updates the [OrderAmount] and [OrderQuantity] columns value as zero in the Orders table:

接下来的Update语句将Orders表中的[OrderAmount]和[OrderQuantity]列值更新为零：

UPDATE O
  SET 
      O.OrderAmount = 0, 
      O.OrderQuantity = 0
FROM Customers C
     JOIN Orders O ON C.Customerid = O.CustomerID;

SQL Server更新联接与左联接 (SQL Server Update Join with Left Join)

In previous examples, we use inner join for retrieving records in which similar customer id exists in both Customers and Orders table. We can use left join to get matched row along with an unmatched row from the left-hand side table.

在前面的示例中，我们使用内部联接来检索在“客户”和“订单”表中都存在相似客户ID的记录。 我们可以使用左连接来获取匹配的行以及左侧表中的不匹配的行。

The following Select statement shows the output of a Select statement with Left Join:

以下Select语句显示具有Left Join的Select语句的输出：

SELECT C.CustomerName, 
       C.OrderCount, 
       O.OrderAmount, 
       O.OrderQuantity
FROM Customers c 
left JOIN orders O ON O.Customerid = C.CustomerID;

Here, in the output, we can see Customer John and Dan. The values for [OrderCount], [OrderAmount] and [OrderQuantity] columns are NULL for these customers:

在输出中，我们可以看到客户John和Dan。 这些客户的[OrderCount]，[OrderAmount]和[OrderQuantity]列的值为NULL：

We can use the Update statement with Left Join as well, and it updates the records with NULL values. As highlighted earlier, we cannot use a single Update statement for updating multiple columns from different tables.

我们也可以将Update语句与Left Join一起使用，并使用NULL值更新记录。 如前所述，我们不能使用单个Update语句来更新来自不同表的多个列。

The following update statement updates the [OrderCount] value as zero for customers having [OrderCount] column value NULL:

对于具有[OrderCount]列值为NULL的客户，以下更新语句将[OrderCount]值更新为零：

UPDATE C
  SET 
      C.OrderCount = 0
FROM Customers C
     right JOIN Orders O ON C.Customerid = O.CustomerID
   where Ordercount IS NULL

In the following image, we see the actual execution plan of the above SQL Update statement. It uses the clustered index update operator for updating records in the customer’s table:

在下图中，我们看到了上述SQL Update语句的实际执行计划。 它使用聚簇索引更新运算符来更新客户表中的记录：

The following update statement updates the [OrderAmount] and [OrderQuantity] value as zero for customers having [OrderAmount] column value NULL:

对于具有[OrderAmount]列值为NULL的客户，以下更新语句将[OrderAmount]和[OrderQuantity]值更新为零：

UPDATE O
  SET 
      O.OrderAmount = 0, 
      O.OrderQuantity = 0
FROM Customers C
     left JOIN Orders O ON C.Customerid = O.CustomerID
   where OrderAmount IS NULL

Similarly, we can use the following query with Right Join in the SQL Server Update Join statement:

同样，我们可以在SQL Server Update Join语句中对Right Join使用以下查询：

UPDATE C
  SET 
      C.OrderCount = 0
FROM Customers C
      Right JOIN Orders O ON C.Customerid = O.CustomerID
   where Ordercount IS NULL

结论 (Conclusion)

In this article, we explored SQL Server Update Join for updating tables specified with a Join clause. We should be careful in doing updates in a relational table of production instance as it might cause issues due to an incorrect value, column name, etc.

在本文中，我们探讨了SQL Server Update Join，以更新用Join子句指定的表。 我们应该在生产实例的关系表中进行更新时要小心，因为它可能由于值，列名等错误而引起问题。

翻译自: https://www.sqlshack.com/an-overview-of-sql-server-update-join/