Refresh current data without removing any fields in MySQL

Question

Refresh current data without removing any fields in MySQL

Below is the snippet of 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)
    })
})

I am encountering the following error message:

{
    "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 aim to update only the first_name field without affecting other fields and their existing records.

javascript sql mysql

Answer 1

Answer №1

To determine which values are set in your data, you can utilize the following 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 = ?

The COALESCE() function returns the first non-NULL argument. By using this structure, if any parameter is NULL, each column will revert to its previous value as a default.

Answer 2

To determine which values are set in your data, you can utilize the following 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 = ?

The COALESCE() function returns the first non-NULL argument. By using this structure, if any parameter is NULL, each column will revert to its previous value as a default.

Answer 3

Answer №2

attempt:

app.put("/update/:id", (req, res) => {
    const uniqueID = req.params.id;
    const { first_name, last_name, company, email, phone } = req.body; // Check that req.body holds the latest information

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

    const dataValues = [first_name, last_name, company, email, phone, uniqueID];

    db.query(sqlQuery, dataValues, (err, results) => {
        if (err) return res.send(err);
        return res.json(results);
    });
});

Every field will be modified with a new value or retain its current value if no fresh input is given, hence ensuring records are updated without removing any fields

Answer 4

attempt:

app.put("/update/:id", (req, res) => {
    const uniqueID = req.params.id;
    const { first_name, last_name, company, email, phone } = req.body; // Check that req.body holds the latest information

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

    const dataValues = [first_name, last_name, company, email, phone, uniqueID];

    db.query(sqlQuery, dataValues, (err, results) => {
        if (err) return res.send(err);
        return res.json(results);
    });
});

Every field will be modified with a new value or retain its current value if no fresh input is given, hence ensuring records are updated without removing any fields

Refresh current data without removing any fields in MySQL

Answer №1

Answer №2

Similar questions

Utilizing MYSQL triggers for maintaining identical data across two tables

Is it possible to save an HTML5 Canvas screenshot directly onto the page?

Are you utilizing content loaded through jquery load in your work?

transferring data from a dataset to an Oracle table function using SQL

"Twice the loading of Meteor templates: once with an undefined collection, and once with it

Divide the array object based on the "@" symbol

Struggling to differentiate between Node.js and Angular

Issue with retrieving data using Mysql Query

What is the implication when Typescript indicates that there is no overlap between the types 'a' and 'b'?

Transferring data from one HTML element to another using Angular [Ionic]

Troubleshooting the issue of Concurrency in Java Spring: @Transactional(propagation = Propagation.REQUIRED, isolation = Isolation.SERIALIZABLE) failing to

Is it possible to update the state before initiating an API call that relies on that specific state in React?

A step-by-step guide on creating a unique ticket number sequence in PHP

Unforeseen outcomes of JavaScript when using the let and var keywords

Firefox compatibility issue caused by jQuery's appendTo function disrupting hyperlink functionality

Implementing hashing with resumable.js

Is it possible to make a link_to, which is essentially a normal <a href>, clickable through a div that is also clickable?

SQL Query-employee Identification

Issue with Bootstrap Modal failing to display the dialog box

Incorporate a collection of multiple images into Fancybox