mysql - Node.js 中的目标表中的 INNER JOIN 未更新

Question

我试图将money表spend传递给salary表的subtotals，而且save 表中的 saving 传递到工资表的小计。

1.- 当您第一次在花费和/或储蓄中添加数据时(如果有效)

2 .- 更新或删除 save 或 money 表中的数据时，salary 表中的数据不会更新

我只给出了最初获取数据的其中一个表的示例，因为在另一个表中它是相同的

• 1.- 在这部分中添加一个要花费的数据并且它可以工作:

router.post('/contador', isLoggedIn, async (req, res) => {
    const { spent, description } = req.body;
    const sp = {
        spent,
        description,
        user_id: req.user.id
    };
    console.log(sp);
    await pool.query('INSERT INTO money set ?', [sp]);
    await pool.query('UPDATE salary as sa INNER JOIN money as mo ON sa.user_id = mo.user_id SET sa.subtotals = mo.spent');
    await pool.query('UPDATE salary as sa set sa.totals = sa.subtotals + sa.subtotala');
    await pool.query('UPDATE salary as sa set sa.total = sa.moneywon - sa.totals');
    req.flash('success', 'Gasto agregado');
    res.redirect('/links');
});

• 2.- 在这部分中编辑支出数据并且不在工资表中更新:

router.post('/edit/:id', isLoggedIn, async (req, res) => {
    const { id } = req.params;
    const { spent, description } = req.body;
    const sp = {
        spent,
        description
    };
    await pool.query('UPDATE money set ? WHERE id = ?', [sp, id]);
    await pool.query('UPDATE salary as sa INNER JOIN money as mo ON sa.user_id = mo.user_id SET sa.subtotals = mo.spent');
    await pool.query('UPDATE salary as sa set sa.totals = sa.subtotals + sa.subtotala');
    await pool.query('UPDATE salary as sa set sa.total = sa.moneywon - sa.totals');
    req.flash('success', 'Actualizado');
    res.redirect('/links');
});

我希望在工资表中根据我在金钱表中所花费的行进行更新

不幸的是，我没有收到错误消息

Answer 1

我怀疑存在一些锁定问题，因为 async/await 只能与 Promise 一起使用

router.post('/edit/:id', isLoggedIn, async (req, res) => {
    const { id } = req.params;
    const { spent, description } = req.body;
    const sp = {
        spent,
        description
    };
    await Update_First(sp, id);    
    await pool.query('UPDATE salary as sa INNER JOIN money as mo ON sa.user_id = mo.user_id SET sa.subtotals = mo.spent');
    await pool.query('UPDATE salary as sa set sa.totals = sa.subtotals + sa.subtotala');
    await pool.query('UPDATE salary as sa set sa.total = sa.moneywon - sa.totals');
    req.flash('success', 'Actualizado');
    res.redirect('/links');
});

使用 Promise

var Update_First= function(sp, id){   
    return new Promise(function(resolve,reject){ 
        await pool.query('UPDATE money set ? WHERE id = ?', [sp, id]);
    });
}