Updating multiple rows with node-mysql, NodeJS and Q

Question

I use node-mysql, node-js and Q promises.

I have successfully updated, deleted and inserted individual rows using the above. As well as multiple lines in one expression in my scripting scripts.

However, I need to update several lines with different values ​​(batch mode) either in a single request or in a for loop.

The information on how to use prepared statements in mysql2 (presumably improved on node-mysql) is very scarce and there are no examples, although this should be a natural choice, as well as promises to compensate for node-js asynchronous.

In addition, I have successfully used defered.makeNodeResolver () in various test cases.

I am trying to update multiple rows in a single query with a where clause and changing conditions.

It works when I update one line. However, when I try to update multiple rows with a single query, the records are not updated.

I am ready to move on to using the for loop to perform several updates, and then aggregate the result and send it back from the server to the client, which would be my second preferred choice. And I do not understand why this should not be done in this way, if there are not too many impacts on performance. But I did not find examples for this.

var values = [ { users: "tom", id: 101 }, { users: "george", id: 102 } ]; // var params = [values.users, values.id ]; var sql = 'UPDATE tabletest SET users = ? WHERE id = ?;'; connection.query(sql, [{users: values[0].users}, {id: values[0].id }], defered.makeNodeResolver()); 

The code shown above does not actually update multiple lines. I assume there is an error in my syntax.

But anyway, what is the best approach for this in this particular scenario? Prepared statements, repeated requests in a for loop, or stored procedures?

Answer 1

You can do it as follows:

 var values = [ { users: "tom", id: 101 }, { users: "george", id: 102 } ]; var queries = ''; values.forEach(function (item) { queries += mysql.format("UPDATE tabletest SET users = ? WHERE id = ?; ", item); }); connection.query(queries, defered.makeNodeResolver()); 

To use the multi-operator function, you must enable it for your connection:

 var connection = mysql.createConnection({ ... multipleStatements: true, }); 

Answer 2

I do not think that you can (at least easily / efficiently) update several lines in the form in which you are trying. You will basically need to iterate over your values and do an UPDATE for each object.

Answer 3

This code fragment was taken from vcl.js for node.js, it is written in typescript and provides several update, delete, inseer statment in one transaction.

 export class SQLStatment { sql: string; params: Object } var dbError: string; var execCount: number = 0; function DBexecuteBatchMYSQLSingle(connection: any, SQLStatmentArray: Array<SQLStatment>, index: number, callback: () => void) { execCount++; connection.query(SQLStatmentArray[index].sql, SQLStatmentArray[index].params, function (err, rows, fields) { if (err) dbError = err; if (index + 1 == SQLStatmentArray.length) callback(); else { if (!dbError) { DBexecuteBatchMYSQLSingle(connection, SQLStatmentArray, index + 1, function () { if (index == 0) callback(); }); } } }); } function DBBatchUpdateMYSQL(SQLStatmentArray: Array<SQLStatment>, callback?: (err) => void) { var mysql = require('mysql'); var connection = mysql.createConnection({ host: "host",user: "user",database: "db", port: 1022, password: "pass"}); dbError = null; execCount = 0; connection.beginTransaction(function (err) { if (err && callback) callback("Database error:" + err); else { DBexecuteBatchMYSQLSingle(connection, SQLStatmentArray, 0, () => { if (dbError) { connection.rollback(function () { if (callback) callback("Database error:" + dbError); }); } else { connection.commit(function (err) { if (callback) callback(null); }) } }); } }); } 

Answer 4

You should probably take the next path.

 UPDATE DB.table SET table.row = newValue WHERE table.someColumn in ('columnVal1', 'columnVal2'); 

ex.

 UPDATE DB.Students SET result = "pass" WHERE rollNo in (21, 34, 50);