How to Update a SQL Server Table Using JOIN in UPDATE Statements?

Update SQL Server table using JOIN statement

Question:

In SQL Server, there are two tables: sale and ud. The goal is to update the sale column in the assid table based on the matching value of the ud column in the ud.assid table.

Solution:

To execute an UPDATE statement with JOIN in SQL Server, you can use the following syntax:

<code class="language-sql">UPDATE 目标表
SET 更新列 = 新值
FROM 目标表
INNER JOIN 连接表 ON 连接条件
WHERE 筛选条件;</code>

In this example, 目标表 is ud, 连接表 is sale, 连接条件 is ud.id = sale.udid, and 筛选条件 is the matching assid value.

Therefore, the UPDATE statement becomes:

<code class="language-sql">UPDATE ud
SET assid = sale.assid
FROM ud
INNER JOIN sale ON ud.id = sale.udid
WHERE ud.assid IS NULL;</code>

Note: The WHERE clause is optional and can be used to filter the rows that will be updated. In this case, it filters rows where ud.assid is NULL.

The above is the detailed content of How to Update a SQL Server Table Using JOIN in UPDATE Statements?. For more information, please follow other related articles on the PHP Chinese website!