How do I do a bulk update in mySQL using node.js

Question

i want to update data in bulk i have over 50 rows to be updated in an array of objects in node JS. something like https://github.com/felixge/node-mysql and How do I do a bulk insert in mySQL using node.js

var updateData=[ 
    {a: '15',b: 1,c: '24',d: 9,e: 1,f: 0,g: 0,h: 5850,i: 78 },
    {a: '12',b: 1,c: '21',d: 9,e: 1,f: 0,g: 0,h: 55,i: 78 },
    {a: '13',b: 1,c: '34',d: 9,e: 1,f: 0,g: 0,h: 58,i: 78 },
    {a: '14',b: 1,c: '45',d: 9,e: 1,f: 0,g: 0,h: 585,i:78 },
    {a: '16',b: 1,c: '49',d: 9,e: 1,f: 0,g: 0,h: 85,i: 78 } 
]
my query is : update table set a= updateData.a ,b= updateData.b ,c = updateData.c , d==updateData.d ,e=updateData.e,f=updateData.f where e=updateData.e

Does this answer your question? [Updating multiple rows with node-mysql, NodeJS and Q](https://stackoverflow.com/questions/25552115/updating-multiple-rows-with-node-mysql-nodejs-and-q) — 0xC0DED00D, Nov 10 '19 at 02:30

score 4 · Answer 1 · answered Dec 28 '16 at 08:29

As of I know, there is no direct way to do bulk update records in mySQL. But there is a work around for this - You could execute multiple insert statements and then execute the query to achieve the desired result.

To do this, while creating a connection allow it to execute multiple statements as it is disabled by default.

var connection = mysql.createConnection({
          host     : dbConfig.host,
          user     : dbConfig.user,
          password : dbConfig.password,
          database : dbConfig.database,
          multipleStatements: true
 });

Then, construct the bulk update query in the below syntax by manipulating the inputs you have.

Query1; Query2; Query3;

Say, for Instance,

 update table set a='15', b=1, c='24', d=9, e=1, f=0, g=0, h=5850, i=78;update table set a='12', b=1, c='21', d=9, e=1, f=0, g=0, h=5850, i=78;

Then, execute the query as usual,

connection.query(sqlQuery, params, callback);

Hope this helps.

score 2 · Answer 2 · edited May 23 '17 at 12:33

2

You can accomplish this by enabling the multiple statements feature in your mysql connection. Then you can loop through your updateData and construct mysql statements separated by a ';'. You can see an example of this in this answer.

edited May 23 '17 at 12:33

Community

1
1

answered Sep 28 '16 at 13:51

Michael Burke

21
4

score 0 · Answer 3 · answered Oct 26 '21 at 18:15

It's really not easy to bulk update data using node-MySQL but here you can do an alternative if you can use .map function in the frontend. Let me show you what i did with mine--

just make a single update API and use it like this in your frontend-

 updateData.map((item, key)=>{
    return (
        axios.path('/api/update', {
            a: item.a,
            b: item.b,
            c: item.c
        })
    ).then(()=> console.log('updated'))
    .catch((err)=> console.log(err))
})

score 0 · Answer 4 · answered Apr 14 '22 at 10:10

there are couple ifs, but

if you have a unique constraint on column e
if you have a default values for all columns in the target table which are not affected by this query

then you can use this slightly nasty way:

const sql = `insert into table (a,b,c,d,e,f)
values ?
on duplicate key update
  a = values(a),
  b = values(b),
  c = values(c),
  d = values(d),
  f = values(f)`

the use the query variant with passed values (updateData in your case):

connection.query(sqlString, updateData, callback)

your updateData should be an array of arrays of values to go into a,b,c,d,e,f columns

score -1 · Answer 5 · answered Nov 29 '21 at 18:09

A little late answering, but using MySQL JSON_TABLE can help. Here's a working example:

UPDATE person a
INNER JOIN (
    SELECT
        personId, addressType, addressId 
    FROM JSON_TABLE('
        [
            {"personId": 318, "addressType": "Primary", "addressId": 712},
            {"personId": 319, "addressType": "Shipping", "addressId": 712}
        ]',
        '$[*]' COLUMNS(
            personId INT PATH '$.personId',
            addressType VARCHAR(10) path '$.addressType', 
            addressId INT path '$.addressId')
    ) a) b
ON a.personId = b.personId
SET
    a.addressId = b.addressId,
    a.addressType = b.addressType;

As it’s currently written, your answer is unclear. Please [edit] to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers [in the help center](/help/how-to-answer). — Community, Nov 29 '21 at 19:42

How do I do a bulk update in mySQL using node.js

5 Answers5

Linked