How to Update a Table Field Using SQL Joins?

SQL UPDATE query using JOIN

Suppose you need to update a field in a table based on values ​​retrieved from a join of multiple tables. For example, you might have an item_master (im) table that contains a mf_item_number field that needs to be updated based on data from group_master (gm) and Manufacturer_Master (mm).

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;

To update the mf_item_number field in item_master, you can use the following SQL statement:

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;

This query updates the mf_item_number field in item_master based on the SKU value in group_master, which itself is filtered by a condition involving Manufacturer_Master.

Here is an example of a generic update query using joins:

UPDATE A
SET foo = B.bar
FROM TableA A
JOIN TableB B ON A.col1 = B.colx
WHERE ...;

In this example, the UPDATE clause references table alias A in the FROM clause to update field foo based on the B.bar value retrieved from the join between TableA and TableB.

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