Update table in SQL, WHERE clause multiple fields

Question

Hi I wanna update my tblrestocklog with the minimum stockno and its corresponding productno.

Here's my sample table:

StockNo     ProductNo   Quantity    PurchasedDate   ExpirationDate
1017123002  25373          10         2016-10-22    2017-02-10
1017123003  25370          10         2016-10-22    2018-11-21
1017123006  25370          10         2016-10-22    2018-03-30
1017123005  25370          10         2016-10-22    2018-04-22

Now I want to update Product 25370 with the smallest stock number which is 1017123003.

I tried this query and other stuff but it always gives me a error msg..

UPDATE tblrestocklog 
    SET quantity = 20
    WHERE MIN(stockno) AND productno = 25370;

Answer 1

You can use order by and limit in an update statement:

UPDATE tblrestocklog 
    SET quantity = 20
    WHERE productno = 25370
    ORDER BY stockno ASC
    LIMIT 1;

Answer 2

Here is another approach

UPDATE tblrestocklog A
       JOIN (SELECT Min(stockno) min_stockno,
                    productno
             FROM   tblrestocklog p
             GROUP  BY productno) B
         ON A.productno = B.productno
            AND A.stockno = B.min_stockno
SET    quantity = 20
WHERE  productno = 25370 

You can remove the productno filter from where clause to apply this logic for all the productno