Copy a column to another column within a same table in oracle db. Do I need to specify which data goes where?
database, oracle, sql
Solution
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.
Problem
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; ```