2

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

sll
  • 61,540
  • 22
  • 104
  • 156
Qinnovator
  • 429
  • 1
  • 5
  • 6
  • @Qinnovator: please **update** your original question with the error message by **editing it** - don't post longer text here in comments! Really hard to read.... – marc_s Nov 26 '11 at 14:06
  • this link is useful http://stackoverflow.com/questions/224732/sql-update-from-one-table-to-another-based-on-a-id-match – Mehrdad Nov 26 '11 at 14:08

3 Answers3

3

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:

  • put your table name you want to update into the FROM clause and give it a meaningful table alias (not just a or b) - use that table alias in the UPDATE statement
  • use INNER JOIN (instead of "embedded" JOIN by just having table after table, comma-separated) to spell out your JOIN intention and your JOIN conditions
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
1

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
sll
  • 61,540
  • 22
  • 104
  • 156
0
update  a
set     Price = b.Price
from    DailySales a
        join ItemMaster b on a.ItemName = b.ItemName
Sir Crispalot
  • 4,792
  • 1
  • 39
  • 64
  • Agree with marc_s that using `a` and `b` isn't particularly useful, but I thought it might help the OP understand it better. – Sir Crispalot Nov 26 '11 at 14:07