Update Query - pick up and populate with the highest price

[dastr]

Hallo,
I need to make the following query:
I have two tables: one consists of articles with NO prices, the other one consists of the same articles but with two different prices – ie two lines with two different prices.
I am wondering how I may populate the first table with the higher of the two prices for that product from the second table?
There should be some sort of an update query?
Any suggestions?
Thx

 

[PaulO]

Do you actually need to update the first table?

Perhaps not ...

You can certainly build a SELECT query using the TOTALS utility to GROUP BY the Product Code and MAX the Price ... this will then display each Product with only the highest price.

 

[Guus2005]

One simple answer: Don't.

Use a query instead to avoid redundancy.

select A.ArticleName, Max(P.Price) from Article A inner join Price P on A.ID = P.ArticleID
group by A.ArticleName

This way you never have to update a table when a price changes.

HTH