Node JS mysql deleting multiple rows with an array of arrays

Question

I need to make a query which would be able to delete multiple rows from my table. In order to do that I've created an arrays within array with values which need to be passed to that query. Here is my code:

var deleteRooms = [ [ 3, 23 ], [ 4, 23 ], [ 5, 23 ], [ 2, 23 ]];

connection.query("DELETE FROM rate_plans_rooms WHERE room_id = ? AND rate_plan_id = ? ",
[deleteRooms],function(err,results){
 if(err){return console.log(err)}

 else
 {                                          
    console.log('sended');
 }

});

But every time I receive an error like this:

{ 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 ' (4, 23), (5, 23), (2, 23) AND rate_plan_id
 = ?' at line 1

How can I fix that and send my query properly?

Answer 1

A solution for your problem is to use 'IN' inside your query:

var deleteRooms = [[3,23],[4,23],[5,23], [2,23]];

connection.query("DELETE FROM rate_plans_rooms WHERE (room_id, rate_plan_id) IN (?)",
    [deleteRooms],function(err,results){

    if(err) return console.log(err)
    else console.log('sended');
});

Answer 2

The accepted solution did not work for me as it would give an Error: ER_OPERAND_COLUMNS: Operand should contain 2 column(s) error. Instead, this worked for me:

var deleteRooms = [[3,23],[4,23],[5,23], [2,23]];
queryArray = Array(deleteRooms.length).fill('(?)'));

connection.query("DELETE FROM rate_plans_rooms WHERE (room_id, rate_plan_id) IN ("+queryArray.join(',')+")",
    [deleteRooms],function(err,results){

    if(err) return console.log(err)
    else console.log('sended');
});