CODEWITHSUNDEEP
sqloracle-databaseplsql
shelbypereira
shelbypereira

Reputation: 2245

Oracle: copy row while updating one field for table with many columns

Is there a way to generically copy a row, in particular WITHOUT specifying all the columns.

In my situatoin I have a large table where I would like to copy all the columns except the ID and one other column. In fact data is copied from year to year at the start of the year. The table has 50+ columns so it would be more flexible and robust to change in schema if I did not have to specify all the columns.

This is closely related to the question : copy row while updating one field

In that question Kevin Cline's comment essentially asks my question, but no solution was actually provided for this more general case.

EDIT to provide more detail as requested, here is an example of what is needed:

-- setup test table
create table my_table(pk, v1,v2,v3,v4 ... v50) as
  select 17 pk, 1 v1,2 v2,3 v3... 50 v50 from dual;

On the above table copy the row and set pk to 18 and v2 to 10.

Upvotes: 3

Views: 2169

Answers (1)

Frank Schmitt
Frank Schmitt

Reputation: 30775

An easy way to do this is an anonymous PL/SQL block and the usage of ROWTYPE:

-- setup test table
create table my_table(pk, value) as
  select 17 pk, 'abc' value from dual;

declare
  l_data my_table%rowtype;
begin
  -- fetch the row we want to copy
  select * into l_data from my_table tbl where tbl.pk = 17; 
  -- update all fields that need to change
  l_data.pk := 18;
  -- note the lack of parens around l_data in the next line
  insert into my_table values l_data; 
end;

Upvotes: 7

Related Questions