CODEWITHSUNDEEP
mysql
Vin Venture
Vin Venture

Reputation: 41

How to cast and update a numeric value from string column only where applicable

MYSQL: Error code 1292 when trying to update a varchar column using cast

I need to update a column from the values of a different varchar column. I want it to populate as 0 when there's a non-integer value like 'N/A' but take the value otherwise. Here's what I've tried so far:

update mytable
SET ColumnB =
case when concat('',columnA*1) = columnA
then cast(columnA AS UNSIGNED)
else 0
end
where Row_ID = 1;

I want it to populate it as integer values or 0. But instead, I face the below error: Error Code: 1292. Truncated incorrect DOUBLE value: 'N/A'

Sample columnA values: 1,2,3,N/A,0, ,N/A ColumnB should be: 1,2,3,0,0, ,0

Table structure, sample values, error on Fiddle here

Upvotes: 2

Views: 765

Answers (2)

Derviş Kayımbaşıoğlu
Derviş Kayımbaşıoğlu

Reputation: 30545

Check This:

create table mytable
(
  columnA varchar(10),
  columnB double
);

✓

insert into mytable values ('15.2', null);
insert into mytable values ('15', null);

✓

update mytable
  SET ColumnB =
    case when ceil(columnA) = columnA
    then  ceil(columnA)
    else 0
    end

✓

select * from mytable

columnA | columnB
:------ | ------:
15.2    |       0
15      |      15

db<>fiddle here

For your data:

 update mytable
  SET ColumnB =
    case when REGEXP_LIKE(columnA,'^\\d+(\\.\\d+)?$')
    then  floor(columnA*1)
    else 0
    end

Upvotes: 2

Maksym Fedorov
Maksym Fedorov

Reputation: 6456

You can use CEIL function, values which contain integer will be converted to integer, values which contain string will be converted to 0

For example:

SELECT CEIL('10'); // return 10
SELECT CEIL('N/A'); // return 0

The full query will looked like:

UPDATE mytable
SET ColumnB = CEIL(columnA)
WHERE Row_ID = 1;

If columnA has varchar type you must cast it to CHAR. For example:

UPDATE mytable
SET columnB = CEIL(CAST(columnA AS CHAR(5)))
WHERE Row_ID = 1;

Example in DBFidle

Upvotes: 1

Related Questions