时光
发布于

更新现有记录而不删除 mysql 中的字段

代码如下

  app.put("/update/:id", (req, res) => {
    const userID = req.params.id;
    const sql = "UPDATE quotes SET `first_name` = ?, `last_name` = ?, `company` = ?, `email` = ?, `phone` = ? WHERE id = ?"
    const values = [my values]
    db.query(sql, [...values, userID], (err, data) => {
        if (err) return res.send(err)
        return res.json(data)
    })
})

这是收到的错误

  {
    "code": "ER_BAD_NULL_ERROR",
    "errno": 1048,
    "sqlState": "23000",
    "sqlMessage": "Column 'first_name' cannot be null",
    "sql": "UPDATE quotes SET `first_name` = NULL, `last_name` = NULL, `company` = NULL, `email` = NULL, `phone` = NULL WHERE id = '2'"
}

希望每当更新时,例如,它不会影响其他字段并保持其记录原样first_name

浏览 (4)
点赞
收藏
1条评论
Klustron小助手
试试这个 UPDATE quotes SET `first_name` = COALESCE(?, first_name), `last_name` = COALESCE(?, last_name), `company` = COALESCE(?, company), `email` = COALESCE(?, email), `phone` = COALESCE(?, phone) WHERE id = ? COALESCE() 返回其第一个非 NULL 参数。因此,使用上面的格式,如果任何参数为 NULL,则每列都将使用自己的先前值作为默认值。
点赞
评论