Fix MySQL Error 1292: Truncated incorrect DOUBLE value

You're not crazy — this error makes no sense at first

Error 1292 shows up when MySQL thinks you're shoving a non-numeric string into a column that expects a DOUBLE or DECIMAL. It happens a lot during UPDATE or INSERT queries where you inadvertently mix data types. The real cause is almost always an implicit type conversion MySQL can't handle.

The quick fix: turn off strict mode (but don't)

You can run this and the error vanishes:

SET sql_mode = '';

That disables strict SQL mode. Now MySQL will just truncate the bad value silently and insert 0 or NULL. Don't do this in production. You'll corrupt data and never know it. Use this only to confirm the query works syntactically so you can then fix the underlying type mismatch.

The real fix: find the mismatched column

Here's what's actually happening. MySQL sees something like WHERE some_string_column = 12345. It tries to convert every value in some_string_column to a DOUBLE. If any row has a string like 'abc', the conversion fails and you get error 1292.

Steps to find the culprit:

Look at the WHERE clause and SET clause of your query. Identify columns that could be strings but are compared to or assigned numeric values.
Run a check to find non-numeric data in those columns:

SELECT * FROM your_table
WHERE your_column REGEXP '[a-zA-Z]'
   OR your_column NOT REGEXP '^-?[0-9]+(\.[0-9]+)?$';

That regex catches anything that isn't a valid number (including NULLs and empty strings). The rows this returns are the problem.

Once you've identified the bad data, you have three choices:

Clean the data — UPDATE the rows to valid numbers, or set them to NULL if they can't be fixed.
Change the column type — If the column genuinely stores mixed content (like 'N/A', 'Unknown'), alter it to VARCHAR and stop comparing it to numbers.
Cast explicitly — In your query, wrap the column with CAST(column AS UNSIGNED) or CAST(column AS DECIMAL(10,2)). MySQL will still error on conversion failure, but at least you control which column gets cast.

Why step 3 works

When you write WHERE some_string = 12345, MySQL converts 12345 to a string and compares. But if the column is VARCHAR and has trailing spaces or special characters, the comparison might still fail. More commonly, the error triggers on INSERT or UPDATE where you assign a string value to a numeric column — MySQL tries to parse the string as a number and bails when it finds letters. The root is always a type mismatch.

Less common twists of the same error

1. Triggers and views

I've seen this error pop up on a simple SELECT when a view uses a JOIN that mixes types. Or a trigger fails silently during an INSERT, showing error 1292 hours later. The fix is the same: trace the column types in the underlying tables.

2. Variables with wrong type

If you use a user-defined variable like this:

SET @val = 'abc';
UPDATE table SET int_col = @val;

MySQL tries to set an integer column to 'abc'. You'll get error 1292. Cast the variable before using it: SET @val = CAST(@val AS UNSIGNED);

3. Strict mode vs. non-strict

MySQL 5.7 and 8.0 default to strict mode. That's why you see this error. If you have an old app that ran on MySQL 5.5 (non-strict), upgrading triggers this error everywhere. The fix isn't to disable strict mode — it's to fix the data types in the schema.

Prevention — stop it before it starts

Never store numbers in VARCHAR columns unless you have a very good reason. If you do, always cast explicitly in queries.
Run SELECT @@sql_mode on each MySQL server. If it has STRICT_TRANS_TABLES or STRICT_ALL_TABLES, you'll catch data truncation early. That's a good thing.
When writing ETL scripts or migration queries, wrap numeric comparisons with CAST(column AS DECIMAL(18,2)). This surfaces errors during dev, not at 3 AM.
Check for non-numeric data quarterly with the regex query above. It takes 30 seconds and saves hours of debugging.

Error 1292 is MySQL telling you your data is messy. Clean the data, not the error message.