CODEWITHSUNDEEP
sqloracle12c
Max_Salah
Max_Salah

Reputation: 2497

01722. 00000 - "invalid number" / how to update row correctly?

I have a table foo with colum C1 of type smallint. this table contains values 1 and 2 for C1

Now I have changed the type of C1 from smallint to VARCHAR2.

I have to update the table with the following logic:

for every row where C1 = 1 C1 should be now A

for every row where C1 = 2 C1 should be now B

I use the following statement to update:

update foo 
SET C1 = 'A' 
WHERE C1 =0; 

update foo 
SET C1 = 'B' 
WHERE C1 =1; 

commit;

now I get the following error:

  1. 00000 - "invalid number"

How to update correctly? I use oracle 12C database

Upvotes: 1

Views: 512

Answers (2)

sagi
sagi

Reputation: 40481

Well, you first have to change your column type. If you done it already, that its probably because of the lack of quotes ' , strings should be wrapped with single quote marks :

ALTER TABLE foo 
  MODIFY C1 varchar2(2);

update foo 
SET C1 = CASE WHEN C1 = '0' THEN 'A' 
              WHEN C1 = '1' THEN 'B'
         END

As you see, I combined your two queries into one using CASE EXPRESSION , no need for two different updates. If more then two options of C1 is possible, use a WHERE clause : 

update foo 
SET C1 = CASE WHEN C1 = '0' THEN 'A' 
              WHEN C1 = '1' THEN 'B'
         END
WHERE C1 IN('0','1')

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1270463

Once you change the value to a string, you should use strings for the comparison. So try this version:

update foo 
SET C1 = 'A' 
WHERE C1 = '0'; 

update foo 
SET C1 = 'B' 
WHERE C1 = '1'; 

commit;

I think the where clause is causing the error, because the 'A' values from the first update cannot be converted to a number.

Alternatively, you could do:

update foo
    set c1 = (case when c1 = '0' then 'A' else 'B' end)
    where c1 in ('0', '1');

commit;

Upvotes: 3

Related Questions