Node.js MySQL update multiple rows in one query statement

Question

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?

Gordon Linoff · Accepted Answer · 2017-04-17 00:49:57Z

1

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.

edited Apr 17, 2017 at 0:49

answered Apr 16, 2017 at 11:19

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

codeinprogress Over a year ago

I tried this code, but it only updates the row with 'somerowid1', the somerowid2 row is not updated.

Community · Accepted Answer · 2017-05-23 10:31:02Z

0

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!

edited May 23, 2017 at 10:31

CommunityBot

11 silver badge

answered Apr 16, 2017 at 11:24

Shachar

1,19810 silver badges23 bronze badges

6 Comments

codeinprogress Over a year ago

I tried your solution, although it does not give me any errors, it does not update the rows either.

Shachar Over a year ago

can you post the response you're getting?

codeinprogress Over a year ago

In the "rows" var returned by the callback it shows: {"fieldCount":0,"affectedRows":0,"insertId":0,"serverStatus":2,"warningCount":0,"message":"(Rows matched: 0 Changed: 0 Warnings: 0","protocol41":true,"changedRows":0}

Shachar Over a year ago

are you sure that the id's you supply in your 'idsArray' actually exists?, if you're not getting an error but no rows are effected then maybe you're ids are wrong.. not sure...

codeinprogress Over a year ago

I am reading the ids from the page (the user selects the records he needs to update). The ids are read as: var hReqIdsList = req.body.hReqIdsList. then in the query I do hReqIdsList.join()

|

Collectives™ on Stack Overflow

Node.js MySQL update multiple rows in one query statement

2 Answers 2

1 Comment

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related