var sidebar_align = 'right';
var content_container_margin = parseInt('290px');
var sidebar_width = parseInt('270px');
Unanswered: Remake a DB - big problem
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.
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.
As you see here is a column namde by comments (altought it is used rarely!)
SQL> desc user_tables;
second: If any other purpose,so explain us why a distinct table for every descripton?
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
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.
Last edited by AlanP; 01-26-05 at 06:41.
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 :
Update old_table set old_table.code=(select n.code from new_table n where n.description = description);
- Create foreign keys on these codes with queries like :
- Check everything is ok
Alter table old_table
add constraint fk_oldtable_newtable foreign key (code) references new_table(code);
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 )
Alter table old_table drop column description;
HTH & Regards,
Alan's solution is certainly clearer and is the same as mine, except I strongly recommend you create foreign keys.
Good point from RBARAER, definitely put on foreign keys.
Thank you all,
now I try your solution, and then I'll let you know if it works.
It works perfectly.
Thank you so much!