Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2004
    Location
    Detroit, MI
    Posts
    17

    Angry 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?

    Thanks,
    Jamin
    Last edited by jamin0; 10-26-04 at 18:35. Reason: typo

  2. #2
    Join Date
    Aug 2004
    Posts
    330
    What did you use to copy the data to the temporary location?

    If you want to copy data from one column to another:

    update table1
    set column1 = column2;

    (Make sure the data in column2 is clean and will not violate any constraints in column1)

  3. #3
    Join Date
    Jan 2004
    Location
    Detroit, MI
    Posts
    17
    Will that work if I want to copy the column from a different table?

  4. #4
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    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.

Posting Permissions

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