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)