SQL update query using joins
I have to update a field with a value which is returned by a join of 3 tables.
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?
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
UPDATE A SET foo = B.bar FROM TableA A JOIN TableB B ON A.col1 = B.colx WHERE ...
Read more… Read less…
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.
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
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%'
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.
MySQL: In general, make necessary changes par your requirement:
UPDATE shopping_cart sc LEFT JOIN package pc ON sc. package_id = pc.id SET sc. amount = pc.amount