I have two tables which are linked by product code, in table one I have the product (12000 of them) and in table two I have the same products but with mulitple prices, I want to take the lowest price from table two and update table one with it. Simple? Well I can't figure it out.. :-/

I have included the two approaches tried, maybe I have just missed something small, or maybe its just a mess?

Approach one produced the following error: an aggregate function may not appear in an update statement

UPDATE tbl_one
SET tbl_one.price = MIN(tbl_two.price)
FROM tbl_two JOIN tbl_one.stockcode = tbl_two.stockcode
WHERE tbl_one.stockcode = tbl_two.stockcode

approach two doesn't throw an error, and updates the records but not with the lowest price:

UPDATE tbl_one
SET tbl_one.price= tbl_two.price
FROM tbl_two
WHERE tbl_two.stockcode = tbl_one.stockcode
   SELECT MIN(price)
   FROM tbl_two INNER JOIN tbl_one
      ON tbl_two.stockcode = tbl_one.stockcode
   WHERE tbl_two.stockcode = tbl_one.stockcode

As always any quick help or pointers greatfully received! :)

Thanks in advance

Stuart

You've more or less bracketed the solution, although a JOIN is not necessary.

Let's restate your first attempt in pseudocode:
Now, to get the value of the expression in brackets for a given stockcode:
Now, we combine them. The mechanism we use to combine them is a correlated subquery, and the <somevalue> will be the stockcode in tbl_one that is being updated at the moment:
You need to be careful about NULL here; if tbl_one has a stockcode which is not in tbl_two, the subquery will return NULL and that will update the tbl_one.price. Be sure that's what you want

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com

You wouldn't believe how many different trys I made to do this, so close and yet NOT! Thank you so much for you quick response and help.