How Can I Update a Table in SQL Using Joined Values from Multiple Tables?

Using JOIN to update SQL Server table

Using JOIN to update database tables is a powerful technique for modifying data based on the relationships between multiple tables. In Microsoft SQL Server, the UPDATE statement can be used in conjunction with JOIN to achieve this purpose.

Question:

You need to update a field in the "item_master" table with JOIN values ​​from three other tables ("group_master", "Manufacturer_Master" and "item_master" itself).

Solution:

Step 1: Build JOIN query

<code class="language-sql">SELECT
    im.itemid,
    im.sku AS iSku,
    gm.SKU AS GSKU,
    mm.ManufacturerId AS ManuId,
    mm.ManufacturerName,
    im.mf_item_number,
    mm.ManufacturerID
FROM item_master im
JOIN group_master gm ON im.sku = gm.sku
JOIN Manufacturer_Master mm ON gm.ManufacturerID = mm.ManufacturerID
WHERE im.mf_item_number LIKE 'STA%' AND gm.manufacturerID = 34;</code>

This query retrieves all matching rows from three tables and filters based on specified criteria.

Step 2: Update fields using JOIN

The following UPDATE statement sets the "mf_item_number" field of the "item_master" table to the value of "gm.SKU" using the table alias (im, gm, mm) in the JOIN clause:

<code class="language-sql">UPDATE im
SET mf_item_number = gm.SKU
FROM item_master im
JOIN group_master gm ON im.sku = gm.sku
JOIN Manufacturer_Master mm ON gm.ManufacturerID = mm.ManufacturerID
WHERE im.mf_item_number LIKE 'STA%' AND gm.manufacturerID = 34;</code>

Instructions:

• The target table for the update is "item_master" (alias "im").
• The value to be updated is "gm.SKU" (from the "group_master" table).
• The JOIN clause ensures that the rows to be updated satisfy the given conditions.
• This query updates all "mf_item_number" fields in the "item_master" table that meet the specified conditions using the corresponding "SKU" value in the "group_master" table.

The above is the detailed content of How Can I Update a Table in SQL Using Joined Values from Multiple Tables?. For more information, please follow other related articles on the PHP Chinese website!