I want to perform a query Of my sql with nodejs-CodePudding

I have a query which has 2 tables First is prodstock table and second is stockmaster so now I want to perform a query using a function in that when I call one function I want to update both tables by two queries but I can't which I am showing more widely in following

This is stockmaster table

This is prodstock table

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 when I perform poststock function I also want to execute updateprodStock so that in one function stock will update and the total qty will be updated but is not working does anybody can help??

CodePudding user response：

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

CodePudding user response：

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 .....