javascript - How to UPDATE a mySQL table with JSON Object in Node.js? - Stack Overflow

admin管理员组

文章数量:1393874

I have a JSON Object and want to UPDATE a mySQL table, without List all of the Keys

with an INSERT I do this like

    var arrayValue = Object.keys(obj).map(function(key) {
      return String("'"+obj[key]+"'");
    });

    var arrayKeys = Object.keys(obj).map(function(key) {
      return String(key);
    });
    var sql = "INSERT INTO `modules` "+ " (" +arrayKeys.join() + ") VALUES ("+arrayValue.join()+");";
    con.query(sql, function (err, result, fields) {
      if (err) throw err;
      return result;
    });

The same i would do with UPDATE

How can i do this?

I have a JSON Object and want to UPDATE a mySQL table, without List all of the Keys

with an INSERT I do this like

    var arrayValue = Object.keys(obj).map(function(key) {
      return String("'"+obj[key]+"'");
    });

    var arrayKeys = Object.keys(obj).map(function(key) {
      return String(key);
    });
    var sql = "INSERT INTO `modules` "+ " (" +arrayKeys.join() + ") VALUES ("+arrayValue.join()+");";
    con.query(sql, function (err, result, fields) {
      if (err) throw err;
      return result;
    });

The same i would do with UPDATE

How can i do this?

• javascript
• mysql
• node.js
• typescript

Share Improve this question Follow asked Nov 22, 2019 at 18:26 Michael MitchMichael Mitch 43322 gold badges88 silver badges1919 bronze badges 1

• Can you update the question with the JSON object and what exactly would be your update query is mysql from that json object. – ambianBeing Commented Nov 22, 2019 at 20:05

Add a ment  | 

2 Answers 2

Sorted by: Reset to default 4

does this fill your needs?

const object = {
    id: 1,
    firstName: "John",
    lastName: "Doe"
};

const columns = Object.keys(object);
const values = Object.values(object);

let sql = "INSERT INTO tableName ('" + columns.join("','") +"') VALUES (";

for (let i = 0; i < values.length; i++) {
    sql += "?";
    if (i !== values.length - 1) {
        sql += ",";
    }
}

sql+= ")";

connection.query(sql, values, (error, result, fields) => {
    //do what you must here.
});

For update:

const object = {
    id: 1,
    firstName: "John",
    lastName: "Doe"
};

const columns = Object.keys(object);
const values = Object.values(object);

let sql = "UPDATE tableName SET '" + columns.join("' = ? ,'") +"' = ?";

connection.query(sql, values, (error, result, fields) => {
    //do what you must here.
});

Off course what would you put in the where statement?

I hope this helped.

here the example

/ Update an existing user
app.put('/users/:id', (request, response) => {
    const id = request.params.id;

    pool.query('UPDATE users SET ? WHERE id = ?', [request.body, id], (error, result) => {
        if (error) throw error;

        response.send('User updated successfully.');
    });
});

that works for me

本文标签： javascriptHow to UPDATE a mySQL table with JSON Object in NodejsStack Overflow