CODEWITHSUNDEEP
oracle-database
Kiran
Kiran

Reputation: 199

How copy the data from one column to another column in the same table with different data type in oracle

I want to copy data from VISITSAUTHORIZED VARCHAR2(11) column to VISITORS NUMBER(5) column. Can anyone help?

Upvotes: 0

Views: 145

Answers (3)

Sam1604
Sam1604

Reputation: 1489

If you don't wanna change structure of your table mean,

Use the following query if your VISITSAUTHORIZED length is 5 or less 

UPDATE table_name SET VISITORS=to_number(VISITSAUTHORIZED)

if VISITSAUTHORIZED length is more than 5 mean trim your VISITSAUTHORIZED column then update it like,

UPDATE table_name SET VISITORS=to_number(SUBSTR(VISITSAUTHORIZED,1,5))

Upvotes: 1

René Nyffenegger
René Nyffenegger

Reputation: 40489

alter table <table> add visitors number(5);

update <table> set visitors = visitsauthorized;

alter table <table> drop column visitsauthorized;

Of course, this approach will fail if visitsauthorized contains non-numeric values.

Alternativaly, you might want to use dbms_redefinition, but frankly, my experiences with it were not very positive, at least on 11i (see for example this dba.exchange question)

Upvotes: 1

G one
G one

Reputation: 2729

While coping use the cast() function.For reference

Upvotes: 0

Related Questions