sql - 使用联接更新表时如何修复此 SQL 错误？

Question

代码找到所需的数据并对其执行操作。

checktablesql1 检查购买后是否有库存

checktablesql2 将值插入到新表中，进一步用于其他用途

checktablesql3 使用用户电子邮件 ID 搜索销售

checktablesql4 在购买后用新库存更新库存

checktablesql5 删除购买后的数据

我的问题是 checktablesql4 的 sql 语法是错误的。

---

//This one works, used this to test if my logic works
let checktablesql4 = `SELECT i.Stock,i.Stock-s.NumberItems,s.idMedicineDatabase FROM inventory as i INNER JOIN sales as s ON s.idMedicineDatabase=i.idMedicineDatabase WHERE s.idMedicineDatabase IN (${result.map(r => r.idMedicineDatabase).join(',')}) AND s.customerEmail=? AND i.EmailID=?`;
//This one doesnt work, i get sql error, photo attached below
let checktablesql4 = `UPDATE inventory set i.Stock=i.Stock-s.NumberItems FROM inventory as i INNER JOIN sales as s ON s.idMedicineDatabase=i.idMedicineDatabase WHERE s.idMedicineDatabase IN (${result.map(r => r.idMedicineDatabase).join(',')}) AND s.customerEmail=? AND i.EmailID=?`;
                        

完整代码

let checktablesql1 = `SELECT s.NumberItems,s.idMedicineDatabase,i.Stock-i.minStock-s.NumberItems as AvailableStock from sales as s inner join inventory as i ON i.idMedicineDatabase=s.idMedicineDatabase where s.CustomerEmail=? order by AvailableStock`;
let checktablesql2 = `INSERT into salehistory(idSales,idMedicineDatabase,NumberItems,CustomerEmail,SellSession,SaleDate) SELECT idSales,idMedicineDatabase,NumberItems,CustomerEmail,SellSession,SaleDate FROM sales WHERE CustomerEmail=?`;
let checktablesql3 = `SELECT * from sales where CustomerEmail=?`;
let checktablesql5 = `DELETE FROM sales where CustomerEmail=?`;

app.post('/checktable',(req, res) => {
    const EmailID1=sellEmailID1;
    const userEmailID1="[e-mail]";
    db.query(checktablesql1,[EmailID1], (err, result) => {

        if (err) {
            console.log(err);
        }else{
            console.log("checktablesql1",result)
            if(result[0].AvailableStock>0){
                db.query(checktablesql2,[EmailID1], (err, result) => {

                    if (err) {
                        console.log(err);
                    } else {
                        db.query(checktablesql3,[EmailID1], (err, result) => {

                            if (err) {
                                console.log(err);
                            } else {
                                console.log("checktablesql3",result)

                                //let checktablesql4 = `SELECT i.Stock,i.Stock-s.NumberItems,s.idMedicineDatabase FROM inventory as i INNER JOIN sales as s ON s.idMedicineDatabase=i.idMedicineDatabase WHERE s.idMedicineDatabase IN (${result.map(r => r.idMedicineDatabase).join(',')}) AND s.customerEmail=? AND i.EmailID=?`;
                                let checktablesql4 = `UPDATE inventory set i.Stock=i.Stock-s.NumberItems FROM inventory as i INNER JOIN sales as s ON s.idMedicineDatabase=i.idMedicineDatabase WHERE s.idMedicineDatabase IN (${result.map(r => r.idMedicineDatabase).join(',')}) AND s.customerEmail=? AND i.EmailID=?`;
                                db.query(checktablesql4,[EmailID1,userEmailID1], (err, result) => {

                                    if (err) {
                                        console.log(err);
                                    } else {
                                        console.log("checktablesql4",result)
                                        db.query(checktablesql5,[EmailID1], (err, result) => {
            
                                            if (err) {
                                                console.log(err);
                                            } else {
                                                
                                            }  
                                        })
                                    }
                                    })  
                            }  
                        })
                        
                        }

                })
            }
        }
    })




})

标签： sqlnode.js

Answer 1

From 在这种情况下不起作用 改用这个

let checktablesql4 = `UPDATE inventory as i INNER JOIN sales s ON i.idMedicineDatabase=s.idMedicineDatabase SET i.Stock= i.Stock-s.NumberItems WHERE s.idMedicineDatabase IN (${result.map(r => r.idMedicineDatabase).join(',')}) AND s.customerEmail=? AND i.EmailID=?`;