Results 1 to 8 of 8
  1. #1
    Join Date
    Oct 2004
    Posts
    25

    Unanswered: Remake a DB - big problem

    Hi people,
    I'm facing a big problem with my DB.

    I have some column in my tables of VARCHAR2 type, that are "description" column.

    Now I want to create a new table for every description, and replace the VARCHAR2 with a NUMBER column (the ID of the new tables).

    I can't do manually because there is thousand of different values.
    How can I do???

    Every suggestion is welcome.
    Thank you

  2. #2
    Join Date
    Mar 2004
    Posts
    370
    Hi,
    first: What do you mean of description? Are there some comments about table (or Entity)? If ya so there is no need to that.You can add comments to table structure when you are creating it.
    Code:
      SQL> desc user_tables;
    As you see here is a column namde by comments (altought it is used rarely!)
    second: If any other purpose,so explain us why a distinct table for every descripton?

  3. #3
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    the way to do it would probably be as follows

    1. create the lookup table using

    select rownum text_id, text from
    (
    select distinct text from table_a union
    select distinct text from table_b union
    etc...
    )

    2. use alter table to add a text_id number column to all your tables which have the text field

    3. update table_a x set text_id = (select text_id from lookup a where x.text=a.text)

    4. drop the text column from your tables.

    Alan
    Last edited by AlanP; 01-26-05 at 07:41.

  4. #4
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    I think this should do it :

    - Create your new tables which structure will be like (CODE, DESCRIPTION)

    - Insert all needed data into them

    - Add a new NUMBER column in each old_table, say you name this column CODE.

    - Update each old_table with something like this to put the code corresponding to the DESCRIPTION column in the newly created CODE column :
    Code:
    Update old_table set old_table.code=(select n.code from new_table n where n.description = description);
    - Commit

    - Create foreign keys on these codes with queries like :
    Code:
    Alter table old_table 
    add constraint fk_oldtable_newtable foreign key (code) references new_table(code);
    - Check everything is ok

    - Issue
    Code:
    Alter table old_table drop column description;
    to drop the old column you don't need anymore. (Or don't do it yet if you fear you may have done something wrong )

    HTH & Regards,

    RBARAER

  5. #5
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    Alan's solution is certainly clearer and is the same as mine, except I strongly recommend you create foreign keys.

    Regards,

    RBARAER

  6. #6
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    Good point from RBARAER, definitely put on foreign keys.

    Alan

  7. #7
    Join Date
    Oct 2004
    Posts
    25

    Smile

    Thank you all,
    now I try your solution, and then I'll let you know if it works.

  8. #8
    Join Date
    Oct 2004
    Posts
    25
    It works perfectly.

    Thank you so much!

Posting Permissions

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