Menu
I want to change the datatype (varchar2 to number) of a column in an oracle table and the column is not empty. So what I thought I will do is, create a new column, copy the data from one column to another column. Disable/Drop the previous column and rename the new column.
To copy data between the same columns, I can use:
UPDATE TABLE_NAME SET NEW_COLUMN = TO_NUMBER(OLD_COLUMN);
But what I want to confirm before doing this is, do I need to specify which row's data goes where? Or it will be copied to its adjacent row in the column? What I meant is, do I need to do something like:
UPDATE (SELECT TO_NUMBER(OLD_COLUMN) AS OLDISH, NEW_COLUMN AS NEWISH FROM TABLE_NAME A, TABLE_NAME B WHERE A.ID = B.ID) SET NEWISH = OLDISH;
584
Move or copy just the contents of a cell You can also edit and select cell data in the formula bar. Select the row or column that you want to move or copy. In the cell, click where you want to paste the characters, or double-click another cell to move or copy the data. or press Ctrl+V.
UPDATE table SET columnB = columnA; This will update every row. This will also work if you want to transfer old value to other column and update the first one: UPDATE table SET columnA = 'new value', columnB = columnA . Like other answer says - don't forget the WHERE clause to update only what's needed.
You can just add the new column to the table as nullable, either with SQL Server Management Studio by right clicking on the Table and clicking "Design" or by using an ALTER TABLE statement like this ALTER TABLE TableName ADD NewColumnName DataType NULL .
The operation will be done on the same row for each rows.
Be aware that if you do not want to update all the table rows then you need to add a WHERE clause.
67
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
