The MySQL error Truncated incorrect DOUBLE value is one of the weirdest errors in MySQL.
This is because the error can be caused by some mistakes in your SQL script that has nothing to do with a DOUBLE value.
The error is mostly triggered when there’s a mistake in UPDATE statements script.
Let’s see some example scripts that trigger the error. Suppose you have a database named students with the following data:
+----+---------+---------+-------+--------+-------------+
| id | name | subject | score | gender | student_id |
+----+---------+---------+-------+--------+-------------+
| 1 | Sarah | Math | 9 | male | 12937254892 |
| 2 | Natalia | Math | 8 | female | 08936A58421 |
| 3 | Christ | English | 4 | male | 87463X98107 |
+----+---------+---------+-------+--------+-------------+
One of the mistakes that could trigger the Truncated incorrect DOUBLE value error is when you use the AND clause when updating multiple columns of the table.
The script would look as follows:
UPDATE students
SET name = 'Sarah'
AND score = 9
WHERE id = '1';
ERROR 1292 (22007): Truncated incorrect DOUBLE value: 'Sarah'
While the error is because of the AND clause, the error description will make you think that there’s something wrong with the value 'Sarah'.
To fix the error, you need to replace the AND clause with a comma:
UPDATE students
SET name = 'Sarah',
score = 9
WHERE id = '1';
-- Query OK, 0 rows affected (0.00 sec)
-- Rows matched: 1 Changed: 0 Warnings: 0
Another thing that could trigger this error is if you try to compare a string value that has no number representation with a number value in the WHERE clause.
An example wrong statement could be as shown below:
UPDATE students
SET score = 5
WHERE student_id = 87463298107;
The error response would look as follows:
ERROR 1292 (22007): Truncated incorrect DOUBLE value: '08936A58421'
The error above is because there’s an entry in the student_id table that has no equal number value representation.
The student_id column is a VARCHAR column that can contain a string type of alphanumeric characters or a number type of numeric characters.
When you create a comparison in the WHERE clause that uses the number type, then MySQL will try to convert the column’s string type to number type for the comparison.
In the case of our example, the second row of the student_id column has no equal number value representation:
+-------------+
| student_id |
+-------------+
| 12937254892 |
| 08936A58421 |
| 87463298107 |
+-------------+
The letter 'A' in the second row value causes MySQL unable to cast the value as an integer and do a comparison.
To fix the error, you need to wrap the value in the WHERE clause with quotation marks:
UPDATE students
SET score = 5
WHERE student_id = '87463298107';
-- Query OK, 0 rows affected (0.00 sec)
-- Rows matched: 1 Changed: 0 Warnings: 0
Interestingly, MySQL won’t throw the same error when you run a SELECT statement:
SELECT * FROM students
WHERE student_id = 87463298107;
The above query would return the result set without an error:
+----+--------+---------+-------+--------+-------------+
| id | name | subject | score | gender | student_id |
+----+--------+---------+-------+--------+-------------+
| 3 | Christ | English | 4 | male | 87463298107 |
+----+--------+---------+-------+--------+-------------+
And those are some SQL script mistakes that can trigger the Truncated incorrect DOUBLE value error.
As you can see, the error can be triggered even when you don’t have any column of DOUBLE type or a DOUBLE value in your scripts.
If you found this error and are unable to find what’s wrong with your statements, then I suggest you check if the types used by your columns are the same as the types in your statements.
If you’re using VARCHAR type in your column, then it’s better to compare the column value with a string even though it looks like a number.
When there are values of different types, you can explicitly convert one of the values to match the other using the CAST() function.
Good luck in fixing the error! 👍
