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

Update existing records without deleting fields in mysql

Here is my code

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)
    })
})

Here is the error I have been receiving

{
    "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'"
}

I want whenever I update first_name for example it doesn’t affect other fields and keep its record as it is

Tags:

2

Answers


  1. 0

    If you don’t know which values are set in your data you could use this syntax:

    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() returns its first non-NULL argument. So using the format above, if any parameter is NULL, each column will use its own previous value as a default.

    Login or Signup to reply.
  2. 0

    try:

    app.put("/update/:id", (req, res) => {
    const userID = req.params.id;
    const { first_name, last_name, company, email, phone } = req.body; // Assuming req.body contains the new values

    const sql = `
        UPDATE quotes 
        SET 
            first_name = COALESCE(?, first_name),
            last_name = COALESCE(?, last_name),
            company = COALESCE(?, company),
            email = COALESCE(?, email),
            phone = COALESCE(?, phone)
        WHERE id = ?`;

    const values = [first_name, last_name, company, email, phone, userID];

    db.query(sql, values, (err, data) => {
        if (err) return res.send(err);
        return res.json(data);
    });
});

    each field will either be updated with a new value or keep its existing value if no new value is provided, effectively updating records without deleting fields

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search