Oracle:在更新具有多个列的表的一个字段时复制行 [英] 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.

在我的座位中，我有一张大桌子，我想在其中复制除ID和另一列之外的所有列.实际上，数据是从年初开始逐年复制的.该表有50多个列，因此如果我不必指定所有列，则更改架构将更加灵活和健壮.

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

在这个问题上，凯文·克莱恩(Kevin Cline)的评论本质上是在问我一个问题，但实际上并没有针对这种更普遍的情况提供解决方案.

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;

在上表中复制该行，并将pk设置为18，将v2设置为10.

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

推荐答案

一个简单的方法是使用匿名PL/SQL块和ROWTYPE的用法:

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;

这篇关于Oracle:在更新具有多个列的表的一个字段时复制行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！