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

SQL UPDATE with Multiple Table Joins

Updating a table field using data from multiple joined tables is a frequent SQL operation. This guide demonstrates how to perform this task efficiently.

Scenario:

Imagine a database with three tables: item_master (im), group_master (gm), and Manufacturer_Master (mm). We'll use these tables to illustrate the update process. Sample data retrieval is shown below:

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;

Update Requirement:

The goal is to update the mf_item_number field in item_master with values derived from the joined tables.

SQL Solution:

The following SQL statement accomplishes this update:

UPDATE im
SET mf_item_number = gm.SKU  -- Or other appropriate field from joined tables
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;

Explanation:

• UPDATE im: Specifies the table (item_master aliased as im) to be updated.
• SET mf_item_number = gm.SKU: Assigns the new value to mf_item_number. The new value is sourced from the SKU column in the group_master table (gm). You can replace gm.SKU with any other relevant field from the joined tables.
• FROM item_master im JOIN group_master gm ON im.sku = gm.sku JOIN Manufacturer_Master mm ON gm.ManufacturerID = mm.ManufacturerID: Defines the joins between the three tables based on matching sku and ManufacturerID values.
• WHERE im.mf_item_number LIKE 'STA%' AND gm.manufacturerID = 34: Filters the rows to be updated, ensuring only those matching the specified criteria are affected.

Executing this query updates the mf_item_number field in the item_master table with the corresponding SKU values from group_master, based on the join conditions and WHERE clause filters. Remember to adjust the SET clause and WHERE clause to match your specific update requirements.

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