RSS

Copy rows in the same table with modified data in Oracle

08 Aug

To copy data, modify column values and insert it into the same table we’ll have to modify the primary key and unique columns. Here are 3 different approaches:

The 1st approach:

BEGIN

  FOR r IN (SELECT * FROM MyTable WHERE Column2 = 'Value2') 
  LOOP
    r.Table_ID := PK_Seq.nextval;
    r.Column2  := 'NewValue2';
    r.Column3  := 'NewValue3';
    INSERT INTO MyTable VALUES r;
  END LOOP;

END;


The 2nd approach create a temp table to hold the data:

CREATE TABLE Temp_Table AS SELECT * FROM MyTable where Column2 = 'Value2';
UPDATE Temp_Table SET r.Column2 := 'NewValue2', r.Column3 := 'NewValue3';
INSERT INTO MyTable SELECT * FROM Temp_Table;
DROP TABLE Temp_Table;

The last approach is much less feasible since there might be numerous columns that have to be manually typed, without the help of database tools:

INSERT INTO MyTable
  SELECT PK_Seq.nextval, 'NewValue2', 'NewValue3', Column4, Column5
    FROM MyTable WHERE Column2 = 'Value2'

Reference:
Copying a row in the same table without having to type the 50+ column names (while changing 2 columns)

Advertisements
 
Leave a comment

Posted by on August 8, 2012 in Database, Oracle, SQL

 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: