[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3128
  • Last Modified:

Updating a NUMBER datatype with a decimal value in Oracle

How do I update a datatype NUMBER with a decimal value?

UPDATE myTable SET myNumberVal=0.07

Open in new window


and

UPDATE myTable SET myNumberVal='0.07'

Open in new window


both result in the value being updated as "0"
0
Mike Miller
Asked:
Mike Miller
  • 2
1 Solution
 
slightwv (䄆 Netminder) Commented:
The first one is correct.  If you cannot see the decimal places it is one of two issues:
The table does not allow decimal places or what you are using to query it isn't showing them.

Post the results of:
desc myTable ;


If the column is a whole number like number(10), then you cannot store decimals.

It would need to be defined as something like number(12,2).
0
 
slightwv (䄆 Netminder) Commented:
Here it is in action:
SQL> drop table tab1 purge;

Table dropped.

SQL> create table tab1(col1 number);

Table created.

SQL>
SQL> insert into tab1 values(.07);

1 row created.

SQL> select * from tab1;

      COL1
----------
       .07

SQL> rollback;

Rollback complete.

SQL>
SQL> alter table tab1 modify col1 number(10);

Table altered.

SQL> insert into tab1 values(.07);

1 row created.

SQL> select * from tab1;

      COL1
----------
         0

SQL> rollback;

Rollback complete.

SQL>
SQL> alter table tab1 modify col1 number(12,2);

Table altered.

SQL> insert into tab1 values(.07);

1 row created.

SQL> select * from tab1;

      COL1
----------
       .07

Open in new window

0
 
Mike MillerSoftware EngineerAuthor Commented:
That's what it was. I copied this table from another server and the precision/scale didn't copy over with it. Thanks!
0

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now