How Can I Use JOINs to UPDATE Tables in SQL Server?

Using JOIN for UPDATE statement in SQL Server

In SQL Server, you can combine data from multiple tables using the JOIN operation. This technique works great when you need to update a table based on data from a related table.

To execute an UPDATE statement using JOIN in SQL Server, consider the following scenario:

Scene:

You have two tables, sale and ud, which contain the following columns:

• sale table: id, udid, assid
• ud table: id, assid

You need to update the assid column in the ud table with the corresponding assid value in the sale table, where sale.assid contains the correct value to update ud.assid.

Query:

To do this, you can use the following JOIN-based UPDATE statement:

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

Instructions:

• UPDATE keyword starts the update operation for the ud table.
• The SET clause specifies the column (assid) to be updated and sets it equal to the corresponding value in the sale table (s.assid).
• The FROM clause uses the INNER JOIN keyword to establish a JOIN between the ud and sale tables. The ON clause is used to specify the connection condition (ud.id = s.udid) to ensure that the connection has records with matching id values.
• Updates the selected record in the ud table with the appropriate assid value from the sale table.

The above is the detailed content of How Can I Use JOINs to UPDATE Tables in SQL Server?. For more information, please follow other related articles on the PHP Chinese website!