node.jsaws-lambdanode-mysql2

Column count doesn't match value count at row 1 with delete where in mysql query using nodejs mysql2

I'm using mysql2/promise to execute some database queries in my node script (AWS lambda function). I want to perform a delete where in query to delete multiple rows, but no matter how I bind the value I always seem to get the same error of "Column count doesn't match value count at row 1".

My simplified code looks like this:

const userIds = [45549, 46207];
const deleteQuery = "DELETE FROM user WHERE id IN (?)";
return await pool.query(deleteQuery, [userIds]);

I've tried different things to bind the value, i.e.:

return await pool.query(deleteQuery, [userIds.map(id => [id]]);

return await pool.query(deleteQuery, userIds.map(id => [id]);

But nothing seems to work. How am I supposed to bind an array value to a WHERE IN query?

Solution

  • Column count doesn't match value count at row 1

    This is an INSERT error, which should not be happening during DELETE, unless there's a trigger.

    Check if you have any DELETE triggers on your user table, and if these triggers are performing any malformed INSERT statements.