Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – Running two different SQL queries in a single nodejs function

I’ve got a query with two tables. The stockmaster table comes in second after the prodstock table. Now that I want to update both tables by running two queries, I want to run a query using a function, but I can’t, as I will explain in more detail below.

This is stockmaster table

this is first table

This is prodstock table

enter image description here

here is the code of the function

    export const postStock = (body) => {
    let sql = ` INSERT INTO stockmaster (stocknum, cat_id, user_id, dyenumber, stockQty, price,stockform, remark) VALUES ('${body.stocknum}', '${body.cat_id}', '${body.user_id}', '${body.dyenumber}', '${body.stockQty}', '${body.price}', '${body.stockform}', '${body.remark}')`;
    return sql;   
};

export const updateprodStock = (cat_id, dyenumber, stockQty) => {
    let sql = `UPDATE prodstock JOIN stockmaster ON prodstock.cat_id = '${cat_id}' AND prodstock.dyenumber = '${dyenumber}' SET prodstock.total_qty = prodstock.total_qty + '${stockQty} `
    return sql}

and here where both function are called

static stock = (req, res) => {
        const { cat_id, dyenumber, stockQty } = req.body;
        connection.query(postStock(req.body), (err, result) => {
            if (err) {
                throw new Error(err);
            } else {
                connection.query(updateprodStock(cat_id, dyenumber, stockQty))
                res.status(200).json({
                    code: 1,
                    msg: "success",
                    data: result
                })
            }
        })
    }

So, in order for the stock to be updated and the total quantity to be updated in one function, I also want to run the updateprodStock command when I perform the poststock function. However, this is not working. Can anyone offer assistance?

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    Okay Here I solved this problem by myself I have to call just another query under the first query using function now it looks like following and successfully working it is as follows:-

    static stock = (req, res) => {
  const { cat_id, dyenumber, stockQty } = req.body;
  connection.query(postStock(req.body), (err, result) => {
    if (err) {
      throw new Error(err);
    }
    connection.query(updateprodStock(cat_id, dyenumber, stockQty), (err, result) => {
      if (err) {
        console.log(err)
      }
      res.status(200).send("Product Stock updated successfully")
    })
    res.status(200).json({
      code: 1,
      msg: "success",
      data: "Stock arrived successfully"
    })
  })
}

    I just called another query into the old function and it solved everything .....


  2. 0

    In my opinion, this line is not perfect:

    let sql = 'UPDATE prodstock JOIN stockmaster ON prodstock.cat_id = '${cat_id}' AND prodstock.dyenumber = '${dyenumber}' SET prodstock.total_qty = prodstock.total_qty + '${stockQty} '

    It will be ( after adding variables):

    UPDATE prodstock JOIN stockmaster ON prodstock.cat_id = 3 AND prodstock.dyenumber = 2 SET prodstock.total_qty = prodstock.total_qty + 1

    You can alco consider of using bind variables insteadof literals. Anyway, this update should look like that:

    UPDATE prodstock 
SET total_qty = prodstock.total_qty + 1
where prodstock.cat_id = 3 AND prodstock.dyenumber = 2
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search