views:

7556

answers:

5

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 master with some other value which is joined in the above condition.

How to frame the SQL ?

+1  A: 

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

marc_s
..or use ANSI JOINS in the FROM clause
gbn
Yes please use the ansi joins, you could be in real trouble in an update if you accidentally got a cross join.
HLGEM
Yes, good point - that works too, of course!
marc_s
+13  A: 

Edit: changed to ANSI joins

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
gbn
im is an alias not a table name...
RedFilter
im is an alias, not a table name - and that is exactly what is needed here. +1
David B
Have you tried it. SQL Server accepts it...
gbn
That should be "update item_master ..."
VVS
I'd give you even more upvotes if I could for getting rid of that old style syntax.
HLGEM
@VVS: no, it shouldn't be
gbn
+1  A: 

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%'

Gratzy
A: 

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.

Remus Rusanu
A: 

Google

asd