CODEWITHSUNDEEP
mysql
user3980196
user3980196

Reputation: 533

mysql statement to update dynamically

I have a select statement SELECT item, quantity FROM sometable which returns the following data:

  Item     Quantity
   21         23
   22         12
   23         15

Is it possible to subtract by one, the quantity of the item which is lesser (using an SQL statement). In the above scenario, the quantity of Item 22 should become 11.

Using the statement SELECT item, quantity FROM sometable ORDER BY quantity LIMIT 1, I get the row with the least quantity. Now I want to be able to subtract 1 from it with an UPDATE statement that uses the values from the select statement.

I am currently planning to do this by querying the table values through PHP and the loop through them.

Can this be done directly by constructing an UPDATE SQL statement?

Upvotes: 1

Views: 64

Answers (1)

juergen d
juergen d

Reputation: 204756

update your_table
set quantity = quantity - 1
where item = (SELECT item FROM your_table ORDER BY quantity LIMIT 1)

Upvotes: 2

Related Questions