CODEWITHSUNDEEP
mysqldoubletruncated
Lehren
Lehren

Reputation: 149

Inconsistent MYSQL behaviour when updating table with different column lengths and comparing text to integer

If I have a table like

create table test
(
    id    varchar(255) not null
        primary key,
    price varchar(10)  not null,
    link  varchar(255) not null
);

and it has this data:

id price link
1 50 text
2 50 text
3 50 USD text

and I run a query like update test set link = 'a' where price <= 50;

mysql gives me an error [22001][1292] Data truncation: Truncated incorrect DOUBLE value: '50 USD'

But if I change the column length of price to varchar(255) suddenly the query does not throw an error and works (sort of) updating ALL the columns, including the one where price is 50 USD. Which is odd.

Why? is this a setting? Is this a glitch in mysql? The cutoff for the column length for when it errors vs when it doesn't seems to be completely arbitrary and different on different databases

Upvotes: -1

Views: 43

Answers (1)

SelVazi
SelVazi

Reputation: 16063

This is a way to do it by extracting digits only using REGEXP_SUBSTR

update test set link = 'a' where REGEXP_SUBSTR(price,"[0-9]+") <= 50;

Demo here

Upvotes: 0

Related Questions