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;

Gordon Linoff · Accepted Answer · 2016-10-22 16:14:37Z

2

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;

answered Oct 22, 2016 at 16:14

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Pரதீப் · Accepted Answer · 2016-10-22 16:22:35Z

1

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

edited Oct 22, 2016 at 16:22

answered Oct 22, 2016 at 16:16

Pரதீப்

94.1k21 gold badges144 silver badges179 bronze badges

Comments

R.Joy · Accepted Answer · 2016-10-22 16:56:06Z

0

Try the following query:

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

edited Oct 22, 2016 at 16:56

R.Joy

571 silver badge5 bronze badges

answered Oct 22, 2016 at 16:31

Hadi

37.5k13 gold badges73 silver badges128 bronze badges

Collectives™ on Stack Overflow

Update table in SQL, WHERE clause multiple fields

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related