I'm am trying to prepare this query using node-mysql.
The node-mysql docs suggest that nested arrays, e.g. [[1, 2], [3, 4]] are converted to grouped lists, e.g. (1, 2), (3, 4). Along those lines, I set up my query as follows:
var sql = DELETE FROM collection_post WHERE (post_id, collection_id) IN (?)
var values = [[170, 2], [170, 7]]; // Generated programmatically.
Based on the rules specified in node-mysql, when I run
connection.query(sql, values, callback);
I assumed the resulting query would be:
DELETE FROM collection_post WHERE (post_id, collection_id) IN ((170, 2), (170, 7))
But when I actually perform the query I get this:
ER_OPERAND_COLUMNS: Operand should contain 2 column(s)
I tried removing the parentheses from the sql statement e.g.
var sql = DELETE FROM collection_post WHERE (post_id, collection_id) IN ?
but that results in a syntax error. What's the proper formatting / escaping to get this query to work?
So I figured this out awhile back and thought I'd post my answer here. Turns out you have to wrap the values in another array. So your query would look like this:
var sql = DELETE FROM collection_post WHERE (post_id, collection_id) IN (?);
and your values need to look like this:
var values = [[[170, 2], [170, 7]]];