SQL update query using joins

Updating Fields with SQL Join Queries in MS SQL Server

Have you ever found yourself in a situation where you needed to update a field in a table using values obtained from a join query? Look no further! In this blog post, we will explore how to tackle this problem using MS SQL Server.

The Problem

Let's say we have three tables: item_master, group_master, and Manufacturer_Master. We want to update the mf_item_number field in the item_master table with a value from the join of these three tables.

The Solution

Fear not, for we have the solution to your problem! We can achieve this goal by utilizing the power of SQL join queries and the UPDATE statement. Here's how you can do it:

UPDATE item_master
SET mf_item_number = joined_table.new_value
FROM (
    SELECT im.itemid, new_value
    FROM item_master im
    INNER JOIN group_master gm ON im.sku = gm.sku
    INNER JOIN Manufacturer_Master mm ON gm.ManufacturerID = mm.ManufacturerID
    WHERE im.mf_item_number LIKE 'STA%'
        AND gm.manufacturerID = 34
) AS joined_table
WHERE item_master.itemid = joined_table.itemid

Let's break it down step by step. We start with the UPDATE statement, specifying the table we want to update (item_master). We then use the SET keyword to indicate the field we want to update (mf_item_number) and set it equal to the value we obtain from the join.

To perform the join, we create a subquery (aliased as joined_table) that selects the necessary columns (itemid and new_value) from the join of the three tables. We use the INNER JOIN keyword to specify the relationships between the tables (im.sku = gm.sku and gm.ManufacturerID = mm.ManufacturerID).

Next, we add conditions to the WHERE clause of the subquery to filter the rows we want to update. In our example, we want to update only those rows where mf_item_number is like 'STA%' and gm.manufacturerID is 34.

Finally, we link the subquery with the main UPDATE statement by matching the itemid column in the item_master table with the itemid column in the subquery.

Example

Let's illustrate this solution with the example you provided:

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

The above query fetches the data from the three tables using the join conditions you provided. To update the mf_item_number field of the item_master table with the joined value, simply replace the SELECT statement with the UPDATE statement from the previous solution.

Conclusion and Call-to-Action

Updating fields using join queries in MS SQL Server is a powerful technique that can save you time and effort. By combining the UPDATE statement with the join functionality, you can easily update your data with values obtained from multiple tables.

Next time you find yourself in a similar situation, remember this handy solution and simplify your SQL queries. Give it a try and see the magic happen!

Have you encountered any other challenging SQL problems? Let us know in the comments below and we'll be happy to help you find a solution.

👍 Keep learning, exploring, and mastering SQL queries!

(Share this blog post with your fellow data enthusiasts and SQL geeks to help them overcome their SQL update challenges by clicking the social media icons below!)