CODEWITHSUNDEEP
phpmysql
rakupu
rakupu

Reputation: 119

MYSQL Updating database using maths?

My query: 

UPDATE `settings_products` SET `stock_osrs` = SUM(stock_osrs - 50)

I have a stock system in my admin panel which currently displays stock levels set by the admins, but I am creating an API so every payment that has been completed will take away the stock.

Lets say that the ordered amount is "50" like in the query and I want to take this away from the stock_osrs.

How exactly would I go about doing this?

EDIT: I am aware that I could use PHP to do the calculations and then update the table from that, but I feel this will be less reliable as I would have to do "50,000" - 50 = "45,950" and post that number to the database.

In this time there could have been another order which would cause the code to mess up, hence that I need to use SQL to achieve this!

Upvotes: 0

Views: 59

Answers (4)

jasinth premkumar
jasinth premkumar

Reputation: 1413

use this query this might solve your problem .

you can simply execute mathematical operation directly.

UPDATE settings_product SET stock_osrs=stock_osrs-50;

i have verified the above query it worked ,try it

Upvotes: 0

M.Muzamil Ejaz
M.Muzamil Ejaz

Reputation: 11

I think you want something like this:

UPDATE 
  settings_products AS lg 
  INNER JOIN 
    (SELECT 
      l.`stock_osrs` AS stock_osrs,
      COUNT(l.id) AS total,
      SUM(l.stock_osrs) AS SUM
    FROM
      stock_osrs l 
    GROUP BY l.`stock_osrs`) sub 
    ON lg.`stock_osrs` = sub.stock_osrs SET lg.stock_osrs = sub.sum - 50 ;

I am assuming that primary key of this table is id. May be you can modify it a little bit but this is the sort of thing you are looking for...

Upvotes: -1

isaace
isaace

Reputation: 3429

I think you are just trying to do minus 50 on a field so this should work.

UPDATE `settings_products` SET `stock_osrs` = `stock_osrs` - 50

Upvotes: 0

manassehkatz-Moving 2 Codidact
manassehkatz-Moving 2 Codidact

Reputation: 3034

UPDATE `settings_products` SET `stock_osrs` = `stock_osrs` - 50

SUM() operates on groups of records - e.g., SELECT SUM(stock_osrs) FROM settings_products WHERE... would give you the total of stock_osrs for a group of records. It is possible to use SUM() as well - e.g., to get the SUM() of records in one table and store it in a field in another table, but that gets a bit more complicated. For simple inside-one-record math, just include the equation. You can even include multiple fields - e.g., UPDATE inventory SET available = onhand - onorder or similar statements.

Upvotes: 3

Related Questions