Sql 简明教程
SQL - UPDATE JOIN
使用 SQL 更新单个数据库表中输入的数据时，可以使用 UPDATE 语句。但是，要更新多个数据库表中的数据，我们需要使用 UPDATE… JOIN 子句。
To update the data entered in a single database table using SQL, you can use the UPDATE statement. However, to update the data in multiple database tables, we need to use the UPDATE… JOIN clause.
例如，如果一个学生更改了他们的主要电话号码并希望在他们的组织数据库中更新它，则需要在多个表（如学生记录、实验室记录、食堂通行证等）中修改信息。使用 JOIN 子句，可以将所有这些表合并为一个表，然后使用 UPDATE 语句，可以同时更新它们中的学生数据。
For instance, if a student changes their primary phone number and wishes to update it in their organizational database, the information needs to be modified in multiple tables like student records, laboratory records, canteen passes etc. Using the JOIN clause, you can combine all these tables into one, and then using UPDATE statement, you can update the student data in them simultaneously.
The SQL UPDATE… JOIN Clause
UPDATE 语句只修改单个表中的数据，SQL 中的 JOINS 用于根据一个匹配字段从多个表中提取行组合。
The UPDATE statement only modifies the data in a single table and JOINS in SQL are used to fetch the combination of rows from multiple tables, with respect to a matching field.
如果我们想更新多个表中的数据，可以使用 JOINS 将多个表合并为一个，然后使用 UPDATE 语句对其进行更新。这也被称为跨表修改。
If we want to update data in multiple tables, we can combine multiple tables into one using JOINS and then update them using UPDATE statement. This is also known as cross-table modification.
Syntax
以下是 SQL UPDATE…JOIN 语句的基本语法：
Following is the basic syntax of the SQL UPDATE… JOIN statement −
UPDATE table(s)
JOIN table2 ON table1.join_column = table2.join_column
SET table1.column1 = table2.new_value1,
table1.column2 = table2.new_value2;其中， JOIN 可以是：常规连接、自然连接、内连接、外连接、左连接、右连接、全连接等。
Where, JOIN can be: Regular Join, Natural Join, Inner Join, Outer Join, Left Join, Right Join, Full Join etc.
Example
假设我们使用以下查询创建了一张名为 CUSTOMERS 的表，其中包含客户的个人详细信息，包括他们的姓名、年龄、地址和薪水等：
Assume we have created a table named CUSTOMERS, which contains the personal details of customers including their name, age, address and salary etc., using the following query −
CREATE TABLE CUSTOMERS (
ID INT NOT NULL,
NAME VARCHAR (20) NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR (25),
SALARY DECIMAL (18, 2),
PRIMARY KEY (ID)
);现在，使用如下所示的 INSERT 语句向此表中插入值：
Now, insert values into this table using the INSERT statement as follows −
INSERT INTO CUSTOMERS VALUES
(1, 'Ramesh', 32, 'Ahmedabad', 2000.00 ),
(2, 'Khilan', 25, 'Delhi', 1500.00 ),
(3, 'Kaushik', 23, 'Kota', 2000.00 ),
(4, 'Chaitali', 25, 'Mumbai', 6500.00 ),
(5, 'Hardik', 27, 'Bhopal', 8500.00 ),
(6, 'Komal', 22, 'Hyderabad', 4500.00 ),
(7, 'Muffy', 24, 'Indore', 10000.00 );该表将被创建为：
The table will be created as −
ID |
NAME |
AGE |
ADDRESS |
SALARY |
1 |
Ramesh |
32 |
Ahmedabad |
2000.00 |
2 |
Khilan |
25 |
Delhi |
1500.00 |
3 |
Kaushik |
23 |
Kota |
2000.00 |
4 |
Chaitali |
25 |
Mumbai |
6500.00 |
5 |
Hardik |
27 |
Bhopal |
8500.00 |
6 |
Komal |
22 |
Hyderabad |
4500.00 |
7 |
Muffy |
24 |
Indore |
10000.00 |
让我们创建另一个名为 ORDERS 的表，其中包含所做的订单和下单日期的详细信息。
Let us create another table ORDERS, containing the details of orders made and the date they are made on.
CREATE TABLE ORDERS (
OID INT NOT NULL,
DATE VARCHAR (20) NOT NULL,
CUSTOMER_ID INT NOT NULL,
AMOUNT DECIMAL (18, 2)
);使用 INSERT 语句像下面这样向该表中插入值：
Using the INSERT statement, insert values into this table as follows −
INSERT INTO ORDERS VALUES
(102, '2009-10-08 00:00:00', 3, 3000.00),
(100, '2009-10-08 00:00:00', 3, 1500.00),
(101, '2009-11-20 00:00:00', 2, 1560.00),
(103, '2008-05-20 00:00:00', 4, 2060.00);该表显示如下：
The table is displayed as follows −
OID |
DATE |
CUSTOMER_ID |
AMOUNT |
102 |
2009-10-08 00:00:00 |
3 |
3000.00 |
100 |
2009-10-08 00:00:00 |
3 |
1500.00 |
101 |
2009-11-20 00:00:00 |
2 |
1560.00 |
103 |
2008-05-20 00:00:00 |
4 |
2060.00 |
以下 UPDATE…JOIN 查询根据订单金额 500 的通货膨胀，将客户的工资增加 1000：
Following UPDATE… JOIN query increments the salary of customers by 1000 with respect to the inflation of their order amount by 500 −
UPDATE CUSTOMERS
JOIN ORDERS
ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID
SET CUSTOMERS.SALARY = CUSTOMERS.SALARY + 1000,
ORDERS.AMOUNT = ORDERS.AMOUNT + 500;Verification
我们可以使用如下所示的 SELECT 语句来检索表的内容，从而验证表中是否反映了更改 −
We can verify whether the changes are reflected in a table by retrieving its contents using the SELECT statement as follows −
SELECT * FROM CUSTOMERS;更新后的 CUSTOMERS 表显示如下：
The updated CUSTOMERS table is displayed as follows −
ID |
NAME |
AGE |
ADDRESS |
SALARY |
1 |
Ramesh |
32 |
Ahmedabad |
2000.00 |
2 |
Khilan |
25 |
Delhi |
2500.00 |
3 |
Kaushik |
23 |
Kota |
3000.00 |
4 |
Chaitali |
25 |
Mumbai |
7500.00 |
5 |
Hardik |
27 |
Bhopal |
8500.00 |
6 |
Komal |
22 |
Hyderabad |
4500.00 |
7 |
Muffy |
24 |
Indore |
10000.00 |
现在，检查是否使用以下 SELECT 语句更新了 ORDERS 表：
Now, check whether the ORDERS table is updated using the following SELECT statement −
SELECT * FROM ORDERS;更新后的 ORDERS 表显示如下：
The updated ORDERS table is displayed as follows −
OID |
DATE |
CUSTOMER_ID |
AMOUNT |
102 |
2009-10-08 00:00:00 |
3 |
3500.00 |
100 |
2009-10-08 00:00:00 |
3 |
2000.00 |
101 |
2009-11-20 00:00:00 |
2 |
2060.00 |
103 |
2008-05-20 00:00:00 |
4 |
2560.00 |
UPDATE… JOIN with WHERE Clause
在从多个表中更新记录时，如果我们使用 WHERE 子句和 UPDATE… JOIN 语句，则可以筛选需要更新的记录（来自合并结果集）。
While updating records from multiple tables, if we use the WHERE clause along with the UPDATE… JOIN statement we can filter the records to be updated (from the combined result set).
Syntax
MySQL 数据库中 SQL UPDATE…JOIN 与 WHERE 子句的语法如下：
The syntax of SQL UPDATE… JOIN with WHERE clause in MySQL database is as follows −
UPDATE table(s)
JOIN table2 ON column3 = column4
SET table1.column1 = value1, table1.column2 = value2, ...
WHERE condition;Example
现在，让我们执行以下查询来增加 ID 为 3 的顾客的薪水 -
Now, let us execute the following query to increase the salary of customer whose id is 3 −
UPDATE CUSTOMERS
LEFT JOIN ORDERS
ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID
SET CUSTOMERS.SALARY = CUSTOMERS.SALARY + 1000
WHERE ORDERS.CUSTOMER_ID = 3;Verification
我们可以使用 SELECT 语句检索内容来验证更改是否反映在表中，如下所示。
We can verify whether the changes are reflected in a table by retrieving its contents using the SELECT statement as follows.
SELECT * FROM CUSTOMERS;正如我们在下表中看到的，"Kaushik" 的 SALARY 值增加了 1000 -
As we can see in the table below, SALARY value of "Kaushik" is increased by 1000 −
ID |
NAME |
AGE |
ADDRESS |
SALARY |
1 |
Ramesh |
32 |
Ahmedabad |
2000.00 |
2 |
Khilan |
25 |
Delhi |
1500.00 |
3 |
Kaushik |
23 |
Kota |
3000.00 |
4 |
Chaitali |
25 |
Mumbai |
6500.00 |
5 |
Hardik |
27 |
Bhopal |
8500.00 |
6 |
Komal |
22 |
Hyderabad |
4500.00 |
7 |
Muffy |
24 |
Indore |
10000.00 |
The UPDATE… JOIN Clause in SQL Server
SQL UPDATE…JOIN 子句也适用于 SQL Server 数据库。但是，查询的语法与 MySQL 的语法略有不同。然而，它的工作方式与 MySQL 查询完全相同。
The SQL UPDATE… JOIN Clause also works in SQL Server database. But, the syntax of the query is slightly different from that of MySQL. However, the working of it is exactly the same as MySQL query.
Syntax
以下是在 SQL Server 中 UPDATE… JOIN 的语法 -
Following is the syntax of the UPDATE… JOIN in SQL Server −
UPDATE tables(s)
SET column1 = value1, column2 = value2, ...
FROM table1
JOIN table2 ON table1.join_column = table2.join_column;Example
在此示例中，我们将使用以下 UPDATE…JOIN 查询更新我们在上面创建的 CUSTOMERS 和 ORDERS 表的值 -
In this example, we will update values of the CUSTOMERS and ORDERS table that we created above; using the following UPDATE… JOIN query −
UPDATE CUSTOMERS
SET SALARY = SALARY + 1000
FROM CUSTOMERS
JOIN ORDERS ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID;Verification
我们可以使用 SELECT 语句检索内容来验证更改是否反映在表中，如下所示。
We can verify whether the changes are reflected in a table by retrieving its contents using the SELECT statement as follows.
SELECT * FROM CUSTOMERS;更新后的 CUSTOMERS 表显示如下：
The updated CUSTOMERS table is displayed as follows −
ID |
NAME |
AGE |
ADDRESS |
SALARY |
1 |
Ramesh |
32 |
Ahmedabad |
2000.00 |
2 |
Khilan |
25 |
Delhi |
2500.00 |
3 |
Kaushik |
23 |
Kota |
3000.00 |
4 |
Chaitali |
25 |
Mumbai |
7500.00 |
5 |
Hardik |
27 |
Bhopal |
8500.00 |
6 |
Komal |
22 |
Hyderabad |
4500.00 |
7 |
Muffy |
24 |
Indore |
10000.00 |