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?

Ricardo Faria · Accepted Answer · 2017-06-18 02:22:34Z

21

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');
});

answered Jun 18, 2017 at 2:22

Ricardo Faria

79415 silver badges31 bronze badges

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

Comments

JWMarchant · Accepted Answer · 2021-06-15 08:24:04Z

1

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');
});

edited Jun 15, 2021 at 8:24

answered Jul 16, 2019 at 9:07

JWMarchant

3542 silver badges8 bronze badges

Collectives™ on Stack Overflow

Node JS mysql deleting multiple rows with an array of arrays

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related