Results 1 to 7 of 7
  1. #1
    Join Date
    Dec 2007
    Posts
    3

    Unanswered: want to import varcahr field data in to number field of another table

    i have two table in this i have a field of number type where i want to put the data from varchar field of another table .pls suggest the solution

    is it possible to change the type of a table field .????

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    As long as there are only numeric values in the VARCHAR2 column, there will be no problem; even if you "forget" to apply the TO_NUMBER function, Oracle will perform implicit conversion and do the job:
    Code:
    SQL> CREATE TABLE TAB_1 (id NUMBER, col NUMBER);
    
    Table created.
    
    SQL> CREATE TABLE TAB_2 (id NUMBER, col VARCHAR2(5));
    
    Table created.
    
    SQL> INSERT ALL
      2    INTO TAB_1 (id, col) VALUES (1, NULL)
      3    INTO TAB_1 (id, col) VALUES (2, NULL)
      4    INTO TAB_2 (id, col) VALUES (1, '111')
      5    INTO TAB_2 (id, col) VALUES (2, '222')
      6  SELECT * FROM dual;
    
    4 rows created.
    
    SQL> UPDATE TAB_1 t1 SET
      2    t1.col = (SELECT TO_NUMBER(t2.col) FROM TAB_2 t2
      3              WHERE t2.id = t1.id);
    
    2 rows updated.
    
    SQL> SELECT * FROM TAB_1;
    
            ID        COL
    ---------- ----------
             1        111
             2        222
    
    SQL>
    Column datatype can be modified, but the column must be empty before you do that:
    Code:
    SQL> alter table tab_2 modify (col number);
    alter table tab_2 modify (col number)
                              *
    ERROR at line 1:
    ORA-01439: column to be modified must be empty to change datatype
    
    
    SQL> update tab_2 set col = null;
    
    2 rows updated.
    
    SQL> alter table tab_2 modify (col number);
    
    Table altered.
    
    SQL>

  3. #3
    Join Date
    Dec 2007
    Posts
    3
    thanks for reply but but if we set col =null it will delete all the stored data , but i do not want to remove the data i want data remain as it is .

  4. #4
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    What stored data? In your first post you've said that there are two tables. One of them contains VARCHAR2 column which has some values, and another table contains NUMBER column. Your question was: how to insert VARCHAR2 column values into the NUMBER column? (Answer: use INSERT statement).

    The second question was: is it possible to change a column datatype. Answer: Yes, only if the column is empty.

    Which table (and column) do you want to modify? Is it the first table with the VARCHAR2 column? If so, there are workarounds but they require several steps.

    You might alter that table, add another (NUMBER?) column and copy data from VARCHAR2 -> NUMBER column. Then set VARCHAR2 column to NULL and alter it. Move data back to the original column (which now has its datatype modified) and drop the additional (temporary) column.

    Another way is to create a new table and set correct datatypes, copy data from the original table to the new one, drop the original table and rename the newly created table to the "original" name. This might be more difficult than the first suggested workaround as there might be object which depend on the table to be dropped (foreign key constraints, triggers, ...).

    What exactly do you want to do? You are not charged by number of letters, so - feel free to describe your current situation and desired one. You may even draw it and attach the scanned piece of a paper. But don't expect to get a satisfactory solution if you conceal information.

  5. #5
    Join Date
    Nov 2007
    Posts
    13
    I think what you want to do is change a type o a column! do i correct?
    if is so create a column temporary with number type update the column with to_number(fild2) on column temporary. nullable the fild2 change the fild type and re-insert the values. after all drop the column.

  6. #6
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Jonnywelker, what's the purpose of rewriting previously given suggestion?

  7. #7
    Join Date
    Nov 2007
    Posts
    13
    Quote Originally Posted by Littlefoot
    Jonnywelker, what's the purpose of rewriting previously given suggestion?
    sorry, I don't read your post.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •