Fixing MySQL ERROR 1292: Truncated incorrect DOUBLE value on UPDATE

The error hits mid-UPDATE

You run what looks like a routine UPDATE. MySQL responds with:

ERROR 1292 (22007): Truncated incorrect DOUBLE value: 'active'

The column exists. The syntax is fine. Nothing obvious is broken. What's happening: MySQL is trying to cast a string to a DOUBLE somewhere in your query. With strict mode on, it refuses to silently swallow the bad value and errors out instead.

Why this happens

Error 1292 fires whenever MySQL tries to convert a string to a numeric type — INT, DOUBLE, FLOAT, or DECIMAL — and the string isn't a valid number. In MySQL 5.6 and earlier, this would silently truncate to 0. Since MySQL 5.7, STRICT_TRANS_TABLES is on by default. That means truncation becomes an error, not a quiet data corruption.

Here's what catches people off guard: the bad value is often in the WHERE clause, not the SET clause.

Debug: find the actual culprit

Step 1 — Check your current sql_mode

SELECT @@sql_mode;

See STRICT_TRANS_TABLES or STRICT_ALL_TABLES in the output? That's why MySQL errors instead of converting silently. Don't disable it — fix the query.

Step 2 — Identify the column types involved

DESCRIBE your_table;
-- or
SHOW COLUMNS FROM your_table;

Check every column referenced in your UPDATE — both SET and WHERE. Any column typed as INT, DOUBLE, FLOAT, or DECIMAL being compared or assigned a string value is a candidate.

Step 3 — Isolate the problem clause

Complex WHERE clause? Run it as a SELECT first:

SELECT * FROM orders
WHERE status = 'active'
  AND category_id = 'electronics';

If category_id is an INT column, this SELECT throws the same 1292. You've found your culprit.

Common scenarios and fixes

Scenario 1: String value in WHERE on a numeric column

Nine times out of ten, this is it. A numeric FK or status code gets filtered with a string literal.

-- Broken: category_id is INT but you're passing a string
UPDATE products
SET price = price * 0.9
WHERE category_id = 'electronics';

-- Fixed: use the actual integer ID
UPDATE products
SET price = price * 0.9
WHERE category_id = 5;

Scenario 2: String assigned to numeric column in SET

-- Broken: stock is INT
UPDATE inventory
SET stock = 'none'
WHERE product_id = 42;

-- Fixed: use 0 or NULL instead
UPDATE inventory
SET stock = 0
WHERE product_id = 42;

-- Or if the column should store text, change the type
ALTER TABLE inventory MODIFY stock VARCHAR(20);

Scenario 3: Logical operator misuse (&&, ||)

MySQL's && and || operators coerce both sides to DOUBLE. Pass a string operand and 1292 fires immediately.

-- Broken: MySQL tries to cast 'active' to DOUBLE
UPDATE orders
SET processed = 1
WHERE is_paid = 1 && status = 'active';

-- Fixed: use AND instead of &&
UPDATE orders
SET processed = 1
WHERE is_paid = 1 AND status = 'active';

Scenario 4: String coming from application/ORM

The query looks fine in isolation. The problem is a parameter bound at runtime — check where your application actually builds that value.

-- Python SQLAlchemy example that breaks
db.execute(
    "UPDATE users SET role_id = :role WHERE id = :id",
    {"role": "admin", "id": user_id}  # role_id is INT, not VARCHAR
)

-- Fixed: pass the correct type
db.execute(
    "UPDATE users SET role_id = :role WHERE id = :id",
    {"role": 3, "id": user_id}  # integer ID for 'admin' role
)

Scenario 5: Arithmetic on mixed-type columns

-- Broken: discount is VARCHAR('10%'), MySQL tries to multiply a string
UPDATE orders
SET final_price = base_price * (1 - discount)
WHERE id = 101;

-- Fixed: strip the '%' and cast explicitly
UPDATE orders
SET final_price = base_price * (1 - CAST(REPLACE(discount, '%', '') AS DECIMAL(5,2)) / 100)
WHERE id = 101;

Temporary workaround if you need to ship NOW

Only reach for this if you know exactly what data is being converted and you're cleaning it up immediately after.

-- Relax strict mode for this session only
SET SESSION sql_mode = REPLACE(@@SESSION.sql_mode, 'STRICT_TRANS_TABLES', '');

-- Run your UPDATE
UPDATE ...

-- Restore strict mode
SET SESSION sql_mode = @@GLOBAL.sql_mode;

Without strict mode, MySQL silently truncates bad conversions to 0. That means price = '10abc' quietly becomes price = 10, and status_code = 'active' becomes status_code = 0. Audit the affected rows the moment the UPDATE finishes.

Verify the fix worked

-- 1. Run the UPDATE without errors
UPDATE orders SET processed = 1 WHERE is_paid = 1 AND status = 'active';
-- Query OK, N rows affected (0.01 sec)

-- 2. Confirm the data changed correctly
SELECT id, processed, status FROM orders
WHERE is_paid = 1 AND status = 'active'
LIMIT 10;

-- 3. Check no rows were accidentally zeroed out
SELECT COUNT(*) FROM orders WHERE processed = 0 AND is_paid = 1 AND status = 'active';
-- Should return 0

Long-term: prevent this from happening again

• Keep strict mode on. It catches type mismatches before they silently corrupt data. Running without it in production is how you end up with price = 0 across thousands of rows and nothing in the error logs.
• Match types at the application layer. If a column is INT, always bind an integer — never rely on MySQL's implicit casting to paper over the difference.
• Test complex WHERE clauses as SELECT first before running the UPDATE against a production table.
• In ORMs, audit column mappings. A string field on the model mapped to an INT column in the DB passes type checks in application code, then breaks silently at the DB layer.
• Use CAST() explicitly when mixing types in expressions — it makes the conversion visible and intentional, and it's obvious to the next person reading the query.