Copy & modify Oracle record using %ROWTYPE

Question

I don't have access, right now, to test this, but is the following -- or something like it, as my off-the-top-of-my-head code mightn't be perfect! -- possible in Oracle:

declare
  myRecord myTable%ROWTYPE;
begin
  select * into myRecord from myTable where key = 123;
  myRecord.key   := 456;
  myRecord.value := myRecord.value + 50;
  insert into myTable select * from table(myRecord);
end;

i.e., We copy the record from myTable -- which may have, say, 100 fields -- with key 123 into a variable with the same schema, then update a few of the copied record's fields (e.g., here, a new key and an updated value) before inserting it back into the original table: Effectively, duplicating the original record with some modifications, where necessary?

I know there are other ways to do this, but this seems quite neat, in comparison to what else I've seen...if it were to work, of course!

Answer 1

INSERT INTO myTable VALUES myRecord;

Some good examples on this site: http://psoug.org/reference/insert.html I guess you don't want to do something like this because you have a lot of columns?

INSERT INTO myTable
  SELECT 456, value+50, colx, coly
     FROM myTable
     WHERE key = 123;

Answer 2

It's actually even easier than your proposed syntax

Set up the table and the data

CREATE TABLE foo( 
  col1 NUMBER,
  col2 VARCHAR2(100)
);

INSERT INTO foo( col1, col2 )
  VALUES( 1, 'Justin' );

The PL/SQL block to select, modify, and then re-insert a record

declare
  l_foo_rec foo%rowtype;
begin
  select *
    into l_foo_rec
    from foo
   where col1 = 1;
  l_foo_rec.col2 := 'Michael';
  l_foo_rec.col1 := l_foo_rec.col1 + 1;
  insert into foo
    values l_foo_rec;
end;

Which, when you run it, will produce this data in FOO

  1* select * from foo
SQL> /

      COL1 COL2
---------- --------------------
         1 Justin
         2 Michael

Of course, as Glenn points out, it is generally going to be more efficient to write a SQL statement that inserts a new row by selecting data from an existing row without needed to use PL/SQL at all. Depending on how complex your logic is, however, a PL/SQL approach may be easier to debug and maintain.