CODEWITHSUNDEEP
sqloracle-database
Baljeet
Baljeet

Reputation: 438

To_number fails to convert varchar to number in update statement

I have a table test_table as below

test_id varchar2(10)
new_test_id number

Though test_id is varchar it stored numbers, now I want to store the negative of the current value in test_id to new_test_id. The statement I tried is as below:

update test_table a set a.new_test_id = TO_NUMBER(-a.test_id);

This throws the invalid number error although on running a test query as 

select TO_NUMBER(-test_id) from test_table

it shows properly formatted number results.

Why does to_number fails to convert string to number in the update statement where as it works fine in select? And what is the alternative in this scenario?

Upvotes: 0

Views: 2570

Answers (4)

Baljeet
Baljeet

Reputation: 438

I just figured out it was not working because the test_id column unexpectedly had a couple of records with alphabets. To_number was failing for only those records under update and was fine for others which had numbers.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269973

Try putting the minus sign outside the to_number():

update test_table a set a.new_test_id = - TO_NUMBER(a.test_id);

Upvotes: 1

paul
paul

Reputation: 22001

update test_table a set a.new_test_id = -1 * TO_NUMBER(a.test_id);

Upvotes: 1

juergen d
juergen d

Reputation: 204784

update test_table a 
set a.new_test_id = 0 - TO_NUMBER(test_id)

Upvotes: 0

Related Questions