Unanswered: replace a column with the contents of another with SQL?
Is there any way to replace the contents of a column with the contents of another column with SQL?
For instance, suppose I want to decrease the field size of the primary key from 15 to 10. In order to do that with Oracle I would need to copy the column data to a temporary location, null out the original column, decrease the size, then copy the keys back.
I am having trouble with the last step. I can't seem to figure out how to copy the keys back into there realitive locations.
Can anyone offer any help?
Last edited by jamin0; 10-26-04 at 17:35.
Urquel's solution would work if you altered the table by adding another column, copy your primary key column into the new one, decrease size of the primary key column and update your table by copying new column value into the primary key column.
If you don't want to alter the table that way, would you consider this: create table as select * From the original table; truncate the original one, decrease primary key column, insert into original table select * from new table.
But, you might encounter problems if the primary key values are referenced by foreign keys (temporarily remove bindings and enable them after import is done).
Another way: export the table, truncate it, decrease size of the primary key column, import data (using IGNORE=Y). Same thing with referential integritiy as above, of course.