How to Update SQL Server Tables Using JOIN?

Updating SQL Server Tables Efficiently with JOINs

SQL Server offers a powerful method for updating data across multiple tables: the UPDATE statement combined with a JOIN. This technique is invaluable when you need to modify data in one table based on information residing in another.

Let's illustrate with an example. Imagine two tables, sale and ud, linked by a common identifier. Our objective is to synchronize the assid column in the ud table with matching values from the sale table.

Here's the SQL query that accomplishes this:

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

This query breaks down as follows:

• UPDATE u: This designates the ud table (aliased as u) as the target for the update.
• SET u.assid = s.assid: This specifies that the assid column in the ud table (u.assid) should be updated with the corresponding assid value from the sale table (s.assid).
• FROM ud u INNER JOIN sale s ON u.id = s.udid: This joins the ud and sale tables (aliased as u and s respectively) using an INNER JOIN. The ON clause defines the join condition: only rows where u.id matches s.udid will be considered for the update.

This approach ensures that only matching records are updated, maintaining data integrity and efficiency. The assid values in the ud table are updated to reflect the corresponding values in the sale table where the IDs align.

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