R.Joy R.Joy - 1 month ago 8
SQL Question

Update table in SQL, WHERE clause multiple fields

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

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

Comments