Using MAX and MIN

[halifaxer]

Oh my head's melted. Help would be appreciated.

Right, the following code:



$query = 'SELECT autoInc, productId, COUNT(*) FROM stock WHERE in_stock = "yes" GROUP BY productId HAVING COUNT(*) = "1"';
$results = mysql_query($query) or die(mysql_error());
while($row = mysql_fetch_array( $results )) {

mysql_query('UPDATE stock SET for_sale = "yes" WHERE autoInc = "'.$row["autoInc"].'"') or die(mysql_error()); }


This works wonderfully where there is only 1 product from the group in_stock as there's only 1 to update as for_sale.

However, when we have 2 or more with the same price - we only need one updated as for_sale and either will do, but not both.



$query = 'SELECT autoInc, MIN(price), search_price, brand, product, productId, COUNT(*) FROM stock WHERE in_stock = "yes" GROUP BY productId HAVING COUNT(*) > "1"';
$results = mysql_query($query) or die(mysql_error());
while($row = mysql_fetch_array( $results )) {

...


The update statement is the problem!



mysql_query('UPDATE stock SET for_sale = "yes" WHERE autoInc = "'.$row["autoInc"].'" AND price = "'.$row["MIN(price)"].'"') or die(mysql_error()); }


This updates both rows and I can't stick a LIMIT on an UPDATE so they both go to for_sale. How do I limit it to just 1?

[kuau]

At the risk of revealing my ignorance, could this work?...


UPDATE stock SET for_sale = "yes" WHERE autoInc IN (SELECT DISTINCT autoInc FROM stock WHERE in_stock = "yes" AND price= "'.$row["MIN(price)"].'"