Node JS mysql deleting multiple rows by array of arrays
I need to make a query that will be able to delete multiple rows from my table. To do this, I created arrays in an array with the values that need to be passed to this request. 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 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 ' (4, 23), (5, 23), (2, 23) AND rate_plan_id
= ?' at line 1
How can I fix this and submit my request correctly?
+4
source to share
2 answers
The solution to 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');
});
+6
source to share
Error: ER_OPERAND_COLUMNS: Operand should contain 2 column(s)
the solution doesn't work for me as it is a Error: ER_OPERAND_COLUMNS: Operand should contain 2 column(s)
bug Error: ER_OPERAND_COLUMNS: Operand should contain 2 column(s)
. Instead, this worked for me:
var deleteRooms = [[3,23],[4,23],[5,23], [2,23]];
queryArray = Array(deleteRoons.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');
});
0
source to share