CODEWITHSUNDEEP
mysqlnode.js
Stuti Mohindra
Stuti Mohindra

Reputation: 51

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

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

Upvotes: 5

Views: 15116

Answers (4)

kekert
kekert

Reputation: 966

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

Upvotes: -1

Martin Spurr
Martin Spurr

Reputation: 1

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;

Upvotes: -1

ChandrasekarG
ChandrasekarG

Reputation: 1383

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.

Upvotes: 4

Michael Burke
Michael Burke

Reputation: 21

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.

Upvotes: 2

Related Questions