update from max of other table :-(
-
Hi all,
I am trying to update a table from what is probably a simple script but it has been 'doing my head in' for the last 2 hours!!!
I have a table (stock) that contains fields: product,price.
I want to populate (update) the price with the MAX value from a different table (suppliers) where the product codes are the same in both
ie:
update stock set price = max(supplier.price) where stock.product=supplier.product
Bear in mind, the supplier table will have many occurences of the product (hence wanting the MAX).
When i try this, it doesn't like using the aggregate across the queiries i have attempted.
please,please, nay - pretty please help or i'll go from
to 
to 
to 
Thanks in advance,
Mark
-
How bout
UPDATE S1 SET Price = S2.Price
FROM dbo.Stock S1
INNER JOIN (SELECT Product, MAX(Price) Price
FROM dbo.Supplier
GROUP BY Product) S2
ON S1.Product = S2.Product
Good Hunting!AJ Ahrens
webmaster@kritter.net -
You the man!
Perfectly simple, exactly what i was after.
Thanks very much for that AJ
Mark
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply