Ethan Ethan - 5 months ago 17
SQL Question

Truncated incorrect DOUBLE value using IN on my WHERE

I haven't been able to find an answer to this thus far... Maybe someone here can help.

I'm getting the following error: Error running query... ::::Error: ER_TRUNCATED_WRONG_VALUE: Truncated incorrect DOUBLE value: '2,4'

This happens when a query is run using the mysql module in nodejs. The query is as follows:

selectQ = "UPDATE usr SET pass = ?, last_updator = ?, last_update = NOW() WHERE id IN (?);"


I am calling it like this:

con.query(selectQ,[hash,lupdator,usrs], function (err, rows) {...});


This works fine as long as I only have one number in the usrs variable, but when I have a list of numbers (in a joined string which looks like "2,4") I get the truncation error above.

Any ideas? Thanks!

Answer

The way you're passing the parameter won't give you this:

UPDATE usr SET pass = ?, last_updator = ?, last_update = NOW() WHERE id IN (2,4);

It will give you this:

UPDATE usr SET pass = ?, last_updator = ?, last_update = NOW() WHERE id IN ((2,4));

So the 2,4 becomes one single value. You will need to find another way, which probably means executing separate statements like this one:

UPDATE usr SET pass = ?, last_updator = ?, last_update = NOW() WHERE id = ?;

Also, if you are building that list dynamically, you should know that long lists of values inside IN statements don't scale well. And many DB servers impose limits on their size as well (MSSQL limits to 512 elements, I don't know about MySQL).