I am using node-mysql, node-js, and Q promises.
I have successfully updated, deleted, and inserted single rows using the above. As well as inserted multiple rows in a single statement in my test case scenario.
However, I need to update multiple rows with different values (batch mode) either in a single query or in a for loop.
The information on how to use prepared statements in mysql2 (supposed to improve on node-mysql) is very sparse and there are no examples, although that should be the natural choice, together with promises to compensate for node-js asynchronous nature.
In addition, I have successfully used defered.makeNodeResolver() in various test scenarios.
I am trying to update multiple rows in a single query with a where clause and changing conditions.
It is working when I update a single row. However, when I try to update multiple rows with a single query, the records aren't updated.
I am ready to switch to using a for loop to perform multiple updates and then aggregate the result and send it back from the server to the client, which would have been my second preferred choice. And I don't see why it shouldn't be done that way if there isn't too much of a performance hit. But I haven't found any examples for doing it that way.
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 isn't actually updating multiple rows. I imagine there's an error in my syntax.
But anyway, what is the best approach to go about this in this particular scenario? Prepared statements, repeated queries in a for loop, or stored procedures?
Questions : Updating multiple rows with node-mysql, NodeJS and Q
2022-09-30T10:39:11+00:00 2022-09-30T10:39:11+00:00
9493
I am using node-mysql, node-js, and Q anycodings_promise promises.
I have successfully updated, deleted, and anycodings_promise inserted single rows using the above. As anycodings_promise well as inserted multiple rows in a single anycodings_promise statement in my test case scenario.
However, I need to update multiple rows with anycodings_promise different values (batch mode) either in a anycodings_promise single query or in a for loop.
The information on how to use prepared anycodings_promise statements in mysql2 (supposed to improve on anycodings_promise node-mysql) is very sparse and there are no anycodings_promise examples, although that should be the anycodings_promise natural choice, together with promises to anycodings_promise compensate for node-js asynchronous nature.
In addition, I have successfully used anycodings_promise defered.makeNodeResolver() in various test anycodings_promise scenarios.
I am trying to update multiple rows in a anycodings_promise single query with a where clause and anycodings_promise changing conditions.
It is working when I update a single row. anycodings_promise However, when I try to update multiple rows anycodings_promise with a single query, the records aren't anycodings_promise updated.
I am ready to switch to using a for loop to anycodings_promise perform multiple updates and then aggregate anycodings_promise the result and send it back from the server anycodings_promise to the client, which would have been my anycodings_promise second preferred choice. And I don't see why anycodings_promise it shouldn't be done that way if there isn't anycodings_promise too much of a performance hit. But I haven't anycodings_promise found any examples for doing it that way. anycodings_promise
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 isn't actually updating anycodings_promise multiple rows. I imagine there's an error in anycodings_promise my syntax.
But anyway, what is the best approach to go anycodings_promise about this in this particular scenario? anycodings_promise Prepared statements, repeated queries in a anycodings_promise for loop, or stored procedures?
Total Answers 7
31
Answers 1 : of Updating multiple rows with node-mysql, NodeJS and Q
You can do it this way:
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 multiple statements feature you anycodings_node-mysql have to enable it for your connection:
var connection = mysql.createConnection({ ... multipleStatements: true, });0
2022-09-30T10:39:11+00:00 2022-09-30T10:39:11+00:00Answer Link
mRahman
1
Answers 2 : of Updating multiple rows with node-mysql, NodeJS and Q
You should probably do following way
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);0
2022-09-30T10:39:11+00:00 2022-09-30T10:39:11+00:00Answer Link
jidam
6
Answers 3 : of Updating multiple rows with node-mysql, NodeJS and Q
Don't know if this problem it's still anycodings_node-mysql relevant but I will give my 2 cents:
The solution that I found is to use anycodings_node-mysql Promise.all
Basically you need to build an array of anycodings_node-mysql promises, where every promise is an anycodings_node-mysql update statement:
const getUpdatePromise = (row) => { return new Promise( (resolve, reject) => { mysqlConnection.query('UPDATE tableName SET foo = ? WHERE bar = ?', [row[0], row[1], (error, results, fields) => { if (error) reject(error) resolve(results) }); }) }
You add this new Promise into an array anycodings_node-mysql and pass the final array with all the anycodings_node-mysql update statements to the Promise.all anycodings_node-mysql function.
It worked for me. Hope it works for anycodings_node-mysql someone else.
0
2022-09-30T10:39:11+00:00 2022-09-30T10:39:11+00:00Answer Link
raja
2
Answers 4 : of Updating multiple rows with node-mysql, NodeJS and Q
let query = ""; query += "UPDATE `DBNAME` SET `field1` = `Value`, `field2` = CASE"; `Field3Array`.forEach((item, index) => { query += " WHEN `field3` = '" + item + "' THEN '" + `Field2Array[index]` + "'"; }); query += " END"; query += " WHERE `field3` IN (" + "'" + `Field3Array`.join("','") + "'" + ")"; sql.query(query, function (err, rows) { if (err) { console.log(err) } else { console.log(rows) } });
In here, field1 & field 2 is needed anycodings_node-mysql be updated and field3 is 'where' anycodings_node-mysql clause. The result is undoubtedly anycodings_node-mysql success. I hope this answer will be of anycodings_node-mysql great help in updating multiple fields anycodings_node-mysql in multiple rows at once using mysql anycodings_node-mysql query.
0
2022-09-30T10:39:11+00:00 2022-09-30T10:39:11+00:00Answer Link
jidam
5
Answers 5 : of Updating multiple rows with node-mysql, NodeJS and Q
I don't think you can (at least anycodings_node-mysql easily/efficiently) update multiple rows anycodings_node-mysql in the way you are trying. You would anycodings_node-mysql basically have to loop over your values anycodings_node-mysql and execute an UPDATE for each object.
0
2022-09-30T10:39:11+00:00 2022-09-30T10:39:11+00:00Answer Link
raja
1
Answers 6 : of Updating multiple rows with node-mysql, NodeJS and Q
this piece of code was taken from vcl.js anycodings_node-mysql for node.js, it is written in typescript anycodings_node-mysql and provides a multiple anycodings_node-mysql update,delete,inseer statment's in a anycodings_node-mysql single 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); }) } }); } }); }0
2022-09-30T10:39:11+00:00 2022-09-30T10:39:11+00:00Answer Link
miraj
4
Answers 7 : of Updating multiple rows with node-mysql, NodeJS and Q
A simpler solution using anycodings_node-mysql multipleStatements: true:
function multiupsert(table, cadena) { return new Promise((resolve, reject) => { var queries = ''; cadena.forEach(function (item) { console.log(item) queries += `UPDATE ${table} SET descripcion = '${item.descripcion}',costo = '${item.costo}',estado = '${item.estado}' WHERE id = ${item.id} ; ` }); let conesM=getConectionMulti(); conesM.connect(); conesM.query(queries, (err1, result, fields) => { if (err1) return reject(err1) resolve(result.insertId); console.log(result); conesM.end(); }); })}
0
2022-09-30T10:39:11+00:00 2022-09-30T10:39:11+00:00Answer Link
jidam