Error: ER_OPERAND_COLUMNS: The operand must contain 1 column (s)
My program creates a table like this:
exports.createTablesPhase2= function(tableprefix,cb){
var s3 = 'CREATE TABLE IF NOT EXISTS productData (
`id` int NOT NULL AUTO_INCREMENT,
`url` varchar(255) NOT NULL,
`baseSite` varchar(255) DEFAULT NULL,
`img_url` varchar(255) DEFAULT NULL,
`name` varchar(255) DEFAULT NULL,
`price` varchar(15) NOT NULL,
`sale_price` varchar(15) DEFAULT NULL,
`description` text DEFAULT NULL,
`extra` varchar(255) DEFAULT NULL,
`brand` varchar(255) DEFAULT NULL,
`colors` varchar(255) DEFAULT NULL,
`sizes` varchar(255) DEFAULT NULL,
`date` date NOT NULL ,
PRIMARY KEY `id` (`id`),UNIQUE `url` (`url`));';
client.query(s3, function(err, result) {
if (err) throw err;
cb();
});
};
This bit works, I just put it there to see the structure.
Then after a while the following function tries to insert values into the db:
exports.insertProducts = function(products,tableprefix,cb){
var values = [];
var date=(new Date()).toISOString().substring(0, 19).replace(/T.*/gi, '');
for (var i = 0; i < products.length; i++) {
var p = products[i];
values.push(['null',p.url,p.baseSite,p.imgUrl,p.name,p.price,p.saleprice,p.description,p.moreInfo,p.brand,p.color,p.sizes,date]);
}
console.log(values);
var sql = "INSERT INTO `productData` VALUES ?
ON DUPLICATE KEY UPDATE `price` = VALUES (`price`),`sale_price` = VALUES (`sale_price`), `date` = VALUES (`date`)";
client.query(sql,[values],function(err){
if (err) throw err;
cb();
});
};
And I get the following error: Error: ER_OPERAND_COLUMNS: The operand must contain 1 column
I've seen this before when doing complex queries, but it seems simple ... I must be missing something really simple. I checked the values and they all look ok.
EDIT: Following the advice in the comments, I tried to add the column names, but didn't change anything. I will leave them in order not to clutter the code.
source to share
If you're doing a lot of pasting with syntax INSERT table VALUES ?
in node-mysql
, make sure that the array of arrays sent to .query()
has all of the values entered accordingly - usually as primitives (strings or numbers).
In your case, one of those elements was an array - and it was wrapped in parens by the query constructor node-mysql
, injecting a string VALUES
into something like ...
VALUES(null, '123', (123, 456)...)
This will force MySQL to throw this annoying error Operand should contain 1 column(s)
.
source to share