Changing Data Type to an existing Column with data already stored

Question

Im using SQL Oracle developer.

I have a table called "Vaccine", Within Vaccine I have a column called 'Vaccine_Number' Currently Vaccine Number is a NUMBER datatype size(2). I need to change this to varchar2(10).

Is this possible without having to lose my data in vaccine number?

possible duplicate of [Changing data type in oracle without deleting the data](http://stackoverflow.com/questions/12173214/changing-data-type-in-oracle-without-deleting-the-data) — John Doyle, Mar 05 '14 at 14:56

score 1 · Answer 1 · edited May 23 '17 at 12:03

1

The following is predicated on you not having any foreign key references on Vaccine_Number:

Add a new column to the table with the datatype that you want.
Run an update statement that sets the value of the new column with the other column's value.

Update Vaccine set New_Vaccine_Number=Vaccine_Number where New_Vaccine_Number is null

Also, you may need to use the To_Char function to convert your number into a varchar.

Delete the other column.
Rename new column to Vaccine_Number

edited May 23 '17 at 12:03

Community

1
1

answered Mar 05 '14 at 14:51

jle

9,316
5
48
67

Thank you, Whats the update statement I need to write? New to SQL, so apologise – Alex Anderson Mar 05 '14 at 14:58

Changing Data Type to an existing Column with data already stored

1 Answers1