I have a Itemmaster table and want to update the price from the itemmaster table into DailySales table.
ItemMaster (ItemName, Price)
DailySales (Date, ItemName, Price)
My query is:
update DailySales a
set Price = (select b.price
from DailySales a, Itemmaster b where a.itemname = b.itemname)
The above query fails. Thanks in advance
You didn't tell us much about how it fails (error message or anything) - but I believe this statement ought to work:
UPDATE ds
SET ds.Price = im.Price
FROM dbo.DailySales ds
INNER JOIN dbo.Itemmaster im ON im.itemname = ds.itemname
Points to remember:
FROM clause and give it a meaningful table alias (not just a or b) - use that table alias in the UPDATE statementINNER JOIN (instead of "embedded" JOIN by just having table after table, comma-separated) to spell out your JOIN intention and your JOIN conditions
I assume your query fail because you are trying to assign result set (returned by SELECT) to a single column value. You need to use INNER JOIN to specify how each single row from ItemMaster correspond to a row from DailySales table, then you would be able to assign a Price value for the appropriate entries:
UPDATE DailySales
SET ds.Price = im.Price
FROM DailySales ds
INNER JOIN ItemMaster im ON ds.ItemName = im.ItemName
update a
set Price = b.Price
from DailySales a
join ItemMaster b on a.ItemName = b.ItemName
a and b isn't particularly useful, but I thought it might help the OP understand it better.