SQL update query using joins

Question

I have to update a field with a value which is returned by a join of 3 tables.

Example:

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

I want to update the mf_item_number field values of table item_master with some other value which is joined in the above condition.

How can I do this in MS SQL Server?

Solution 1

UPDATE im
SET mf_item_number = gm.SKU --etc
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

To make it clear... The UPDATE clause can refer to an table alias specified in the FROM clause. So im in this case is valid

Generic example

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

Solution 2

Adapting this to MySQL -- there is no FROM clause in UPDATE, but this works:

UPDATE
    item_master im
    JOIN
    group_master gm ON im.sku=gm.sku 
    JOIN
    Manufacturer_Master mm ON gm.ManufacturerID=mm.ManufacturerID
SET
    im.mf_item_number = gm.SKU --etc
WHERE
    im.mf_item_number like 'STA%'
    AND
    gm.manufacturerID=34

Solution 3

One of the easiest way is to use a common table expression (since you're already on SQL 2005):

with cte as (
select
    im.itemid
    ,im.sku as iSku
    ,gm.SKU as GSKU
    ,mm.ManufacturerId as ManuId
    ,mm.ManufacturerName
    ,im.mf_item_number
    ,mm.ManufacturerID
    , <your other field>
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 cte set mf_item_number = <your other field>

The query execution engine will figure out on its own how to update the record.

Solution 4

Did not use your sql above but here is an example of updating a table based on a join statement.

UPDATE p
SET    p.category = c.category
FROM   products p
       INNER JOIN prodductcatagories pg
            ON  p.productid = pg.productid
       INNER JOIN categories c
            ON  pg.categoryid = c.cateogryid
WHERE  c.categories LIKE 'whole%'

Solution 5

You can specify additional tables used in determining how and what to update with the "FROM " clause in the UPDATE statement, like this:

update item_master
set mf_item_number = (some value)
from 
   group_master as gm
   join Manufacturar_Master as mm ON ........
where
 .... (your conditions here)

In the WHERE clause, you need to provide the conditions and join operations to bind these tables together.

Marc

View more solutions

Comments

[dburges]

Please stop using those implied joins to begin with. It's a poor technique that leads to incorrect results due to unexpected cross joins. This code style is 18 years out of date

[Dan]

See also SO question ... stackoverflow.com/questions/1293330/…

[ZygD]

..or use ANSI JOINS in the FROM clause

[Dan]

Excellent, the use of the CTE makes it simple to convert the original SELECT into an UPDATE

[Baodad]

Works as long as your SELECT query does not have any aggregates, DISTINCT, etc.

[Adam W]

I usually start with semicolon to terminate previous statement (if any). CTE rocks ! Simple to design complicated query / joined updates. I use it all the time...

[Sliq]

FYI this will NOT work in MySQL (different syntax)! For MySQL have a look at gcbenison's answer

[Auspex]

that's not even syntactically correct, while the much older accepted answer is not only syntactically correct but solves the exact question asked.

[Auspex]

Not, I'm pretty sure, in SQL Server 2005, which is what the question is tagged as, but this is the best (i.e, more standard) way on modern SQL Server versions

[Leandro Bardelli]

@Sliq thanks. In any case this is not a MySQL question.

[Leandro Bardelli]

This is not a MySQL question