Pass array in Mysql query with nodejs

Question

I have a simple query that I want to pass an array inside which has 5 items. I am using the mysql module so I know it can be done but am not doing the synatx right and therefore getting a syntax error.

Below is the query:

`UPDATE table1 SET table1.col=0 WHERE (table1.col2) IN = (?) AND table1.id=(SELECT ...);`,[arr]

//arr = [1,2,3,4,5];

I have tried:

`UPDATE table1 SET table1.col=0 WHERE (table1.col2) IN = (?,?,?,?,?) AND table1.id=(SELECT ...);`,[arr]`

but I still get a syntax error.

What syntax error? Please include the full text of the error in your question, or edit to add it. — tadman
– tadman, Commented Oct 3, 2017 at 0:07
Okay am an idiot, I had forgotten the AND in the WHERE clause (I know its included here) and that was the error, I just saw it and thought it was for the array but now that you made me saw it I realised it, thanks — John James
– John James, Commented Oct 3, 2017 at 0:12

Vedant Goenka · Accepted Answer · 2018-03-14 07:16:37Z

9

Adding on to Bill Karwin's answer, you can also pass an array to the MySQL query against the '?' placeholder in the same way

WHERE table1.col2 IN (?)
//arr = [1,2,3,4,5];

Passing arr along with the query will convert it to the required SQL string. The mysql module uses the 'SqlString.arrayToList' function from 'sqlstring' module internally for the transformation: https://github.com/mysqljs/sqlstring/blob/8f193cae10a2208010102fd50f0b61e869e14dcb/lib/SqlString.js#L60

answered Mar 14, 2018 at 7:16

Vedant Goenka

4514 silver badges5 bronze badges

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

Comments

Antti A · Accepted Answer · 2020-12-30 16:58:19Z

6

In my case, array inside of array is needed to get this working. Just array variable as parameter passed only first number to sql.

Here is an example: (Notice ids inside of array as the second parameter)

var sql = "SELECT * FROM table WHERE ID IN (?)";
var ids = [1,2,3];

pool.query(sql, [ids], function (err, result, fields) {

    if(err) {
        console.log(err);
    }
    else {
        console.log(result);
    }
}

answered Dec 30, 2020 at 16:58

Antti A

5405 silver badges14 bronze badges

Comments

Bill Karwin · Accepted Answer · 2017-10-03 00:11:31Z

3

The syntax of the IN() predicate does not use =.

WHERE (table1.col2) IN = (?,?,?,?,?)

should be

WHERE table1.col2 IN (?,?,?,?,?)

Tip: you can (and should) check syntax yourself in the documentation, so you can get answers more easily than posting to Stack Overflow.

https://dev.mysql.com/doc/refman/5.7/en/comparison-operators.html#function_in

answered Oct 3, 2017 at 0:11

Bill Karwin

567k87 gold badges709 silver badges869 bronze badges

1 Comment

John James Over a year ago

I'll accept the answer since indeed it does not use, I had removed it in my code but forgotten here, also thanks for the tip :)

Collectives™ on Stack Overflow

Pass array in Mysql query with nodejs

3 Answers 3

Comments

Comments

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

1 Comment

Linked

Related