Node JS mysql updating with large query

Question

In my node module I want to make an update query to my sql database. It's relatively large and my synax looks like this:

connection.query("UPDATE myTable SET room_price = ?, 
   adult_minimum = ?, adult_standard = ?, adult_additional = ?, 
   child_additional = ?, close_arrival = ?, close_departure = ?, 
   stop_sale = ?, min_stay = ?, max_stay = ? WHERE hotel_id = ? AND 
   rate_plan = ? AND room = ? AND date = ?",
  [toUpdate],function(err,results){
                if(err){return console.log(err)}
                else
                {
                    res.sendStatus(200);
                }
        });

To push all values I use an array of arrays and sample data in one array looks like that:

[456,100,100,100,100,false,true,false,'1','456',1111,'rate plan name
 three','Room Number One','2017-08-02' ]

And then I get this error:

{ Error: ER_PARSE_ERROR: You have an error in your SQL syntax; check
 the manual that corresponds to your MariaDB server version for the
 right syntax to use near '(789, 100, 100, 100, 100, true, false,
 true, '1', '999', 1111, 'rate plan name t' at line 1
at Query.Sequence._packetToError

I don't know where I could make a syntax mistake since I've created very similar update function before and it worked. Also I typed this query manualy in my db engine and everything worked fine as well. Can a large amount of columns to update could be a problem here?

Perhaps, only `insert` supports `[Array]` syntax. Try to use [**multiply statements**](https://stackoverflow.com/a/41358153/6121703). — Aikon Mogwai, Aug 01 '17 at 13:54

score 1 · Answer 1 · answered Aug 01 '17 at 13:48

No, the large amount of columns does not create problems in update queries.

I am guessing from the error message

rate plan name t

that the string you were originally intending to insert/update

rate plan name three

has been truncated between the letter t and h

Perhaps you inserted a new line at that point?

score 0 · Accepted Answer · answered Aug 01 '17 at 13:50

0

if you have the array of arrays, you should run query for each array

big_array.forEach(small_array => {
    connection.query('query', small_array, callback);
})

answered Aug 01 '17 at 13:50

Goolishka

210
1
11

Node JS mysql updating with large query

2 Answers2