CODEWITHSUNDEEP
mysqlnode.js
codeinprogress
codeinprogress

Reputation: 3501

Node.js MySQL update multiple rows in one query statement

I have a table called UserRequests. I need to update the RequestStatus to Completed, Time to current DateTime for a a couple of request records. I am using the node.js mysql module https://github.com/mysqljs/mysql

Usually for a single record update I would do:

connection.query('update UserRequests set RequestStatus = ?, ReqCompletedDateTime = ? where idRequest = ?', ['Completed', new Date(), hReqId], function(err, rows){
    connection.release();
});

But in my case I need to update multiple UserRequests rows with the status of completed and current datetime.

I am getting all the updated request ids in a list. The question is how do I write the query statement that it will update all of them together.

I tried using the multiple query statement but it did not work. Couple of other solutions also did not work. Few othe solutions I tried :

connection.query('update UserRequests set RequestStatus = ?, ReqCompletedDateTime = ? where idRequest = ?', [['Completed', new Date(), 'somerowid1'], ['Completed', new Date(), 'somerowid2']], function(err, rows){
  connection.release();
});

OR

connection.query('update UserRequests set RequestStatus = ?, ReqCompletedDateTime = ? where idRequest = ?', [{'Completed', new Date(), 'somerowid1'}, {'Completed', new Date(), 'somerowid2'}], function(err, rows){
  connection.release();
});

What am I doing wrong?

Upvotes: 0

Views: 9083

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269873

I am not aware that you can pass separate lists of parameters to a query in any language. If you could, it would generate multiple queries to the database.

You can do this by putting all the values in an in list. The query would look like:

update UserRequests
    set RequestStatus = ?,
        ReqCompletedDateTime = now()
    where idRequest in (?, ?);

In the code:

connection.query('update UserRequests set RequestStatus = ?, ReqCompletedDateTime = now() where idRequest in (?, ?)', ['Completed',  'somerowid1', 'somerowid2'], function(err, rows) {

Unfortunately, you cannot parameterize the list of values to the in. Either you need a separate placeholder for each value or you need to insert the list directly into the query string (not recommended).

Also note that ReqCompletedDateTime uses the database now() function rather than the date/time from the application. This ensures that the column is consistent, and not affected by the clocks on other machines.

Upvotes: 1

Shachar
Shachar

Reputation: 1168

You are looking for the 'IN' operator, so try something like:

connection.query('update UserRequests set RequestStatus = ?, ReqCompletedDateTime = ? where idRequest IN (?)', ['Completed', new Date(), idsArray.join()], function(err, rows){
  connection.release();
});

Youre 'idsArray' should look like this:

idsArray = [1,2,3,4];

the 'join' function will convert the array to a string

take a look at this answer: update in

  • note that I didn't tested this code specifically for your case but it should work!

good luck!

Upvotes: 0

Related Questions