How to Update Fields in MS SQL Server Using Joined Data?

Updating Multiple Table Fields in MS SQL Server via JOINs

Efficiently update fields across multiple tables in MS SQL Server using UPDATE statements combined with JOIN operations. Consider this scenario: you need to modify data in the item_master table based on related information in group_master and Manufacturer_Master tables.

Here's a sample query demonstrating the selection process:

<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, group_master gm, Manufacturer_Master mm
WHERE
    im.mf_item_number LIKE 'STA%'
    AND im.sku = gm.sku
    AND gm.ManufacturerID = mm.ManufacturerID
    AND gm.manufacturerID = 34;</code>

Now, let's apply this logic to update the mf_item_number field in item_master:

<code class="language-sql">UPDATE im
SET im.mf_item_number = gm.SKU  -- Update with a value from the joined table
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 UPDATE statement uses JOIN clauses to link the tables. The SET clause assigns the value of gm.SKU to im.mf_item_number for matching records. The WHERE clause filters the update to only those records meeting the specified criteria. Note that you can update mf_item_number with any value derived from the joined tables. This method offers a flexible approach to updating data based on complex inter-table relationships. Remember to always back up your data before running any UPDATE statements.

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