如何复制一组行只更改一列的值而不列出其他列？

Given a table T(x, y, z, t, u, v, ...) is it possible in Oracle to write this query without listing all columns (be it in the SELECT or in the INSERT part)?

给定一个表T(x,y,z,t,u,v,...)是否可以在Oracle中编写此查询而不列出所有列(在SELECT或INSERT部分中)?

INSERT INTO T (x, y, z, t, u, v, ...)
SELECT 'new', y, z, t, u, v, ...
  FROM T
 WHERE x = 'old'

The effect is that all rows for which x has the value of old are duplicated, except that now x has the value of new.

结果是,x具有旧值的所有行都是重复的,除了现在x的值为new。

3 个解决方案

#1

"is it possible in Oracle to write this query without listing all columns (be it in the SELECT or in the INSERT part)"

“在Oracle中是否可以编写此查询而不列出所有列(无论是在SELECT中还是在INSERT部分中)”

No. The only way to avoid typing an explicit projection is to use all the table's columns. You aren't doing that, because you want to use a literal instead of column X. That means you have to list all the other columns in the SELECT projection.

不。避免键入显式投影的唯一方法是使用所有表的列。您没有这样做,因为您想要使用文字而不是列X.这意味着您必须列出SELECT投影中的所有其他列。

Of course, you don't have to specify the columns in the INSERT clause.

当然,您不必在INSERT子句中指定列。

Over the years developers have occasionally wished for an "except" syntax, something like:

多年来,开发人员偶尔希望使用“except”语法,例如:

select * except X from t

but it's never made it into the ANSI standard. In fact, I doubt if it's even been discussed.

但它从未进入ANSI标准。事实上,我怀疑它是否被讨论过。

"PLSQL answers are welcome then!"

“欢迎PLSQL的答案!”

Okay, here is a proof of concept which uses the data dictionary to produce a dynamic insert statement.

好的,这是一个概念证明,它使用数据字典来生成动态插入语句。

It makes the following assumptions:

它做出以下假设:

You only want to substitute the value of one column.

您只想替换一列的值。

The column you want to substitute is a string datatype.

要替换的列是字符串数据类型。

You want to clone all the records in the source table.

您想要克隆源表中的所有记录。

You will need to adjust the code if any of those assumptions are wrong.

如果任何这些假设是错误的,您将需要调整代码。

The procedure loops round the USER_TAB_COLUMNS table, sorting the columns into the table's projected order. It concatenates the column names into the SELECT clause of an INSERT statement, except where the name is that of the substituted column when it concatenates the provided literal instead. Finally it uses Native Dynamic SQL to run the assembled INSERT statement.

该过程循环遍历USER_TAB_COLUMNS表,将列排序为表的预计顺序。它将列名连接到INSERT语句的SELECT子句中,除非在连接提供的文字时,名称是替换列的名称。最后,它使用Native Dynamic SQL来运行汇编的INSERT语句。

create or replace procedure clone_minus_one 
    ( p_sub_col in user_tab_columns.column_name%type
      , p_sub_val in varchar2 )
is
    stmt varchar2(32767) := 'insert into source_table select ';
begin
    for lrec in ( select column_name
                         , column_position 
                  from  user_tab_columns.
                  where table_name = 'SOURCE_TABLE'
                  order by column_position )
    loop
        if lrec.column_position != 1
        then 
            stmt := stmt ||',';
        end if;
        if lrec.column_name != p_sub_col
        then
            stmt := stmt ||lrec.column_name;
        else
            stmt := stmt ||''''||p_sub_val||'''';
        end if;  
    end loop;
    stmt := stmt || ' from source_table';
    execute immediate stmt;
end;
/

#2

You can do exactly as you describe, provided your selected columns are ordered correctly.

如果所选列正确排序,您可以完全按照您的描述进行操作。

The following is Valid

以下是有效的

INSERT INTO T 
SELECT 'new', y, z, t, u, v, ...
  FROM T
 WHERE x = 'old'

or you could try this (Rough script not tested)

或者你可以尝试这个(粗略的脚本未测试)

CREATE TABLE TEMPTABLE AS SELECT * FROM T WHERE X = 'Old';

UPDATE TEMPTABLE SET X='New';

INSERT INTO T (SELECT * FROM TEMPTABLE);

DROP TABLE TEMPTABLE;

#3

The following is going too far to avoid some typing imo, but I like a challenge, so here it goes...

以下是太过分了,以避免一些打字imo,但我喜欢挑战,所以这里...

insert into T
select * from T
where x='old';
commit;

-- update every other row to 'new'
update T
set x='new'
where rowid in
(
select rowid from
  (select rownum rnum, rowid row_id
   from T
   where x='old'
  )
where mod(rnum,2)=0
);
commit;

In 2 steps, but personally I'd just query all_tab_columns for the table (order by column_id) and copy/paste into my script/procedure. But may be some other reason to avoid this, not sure.

分两个步骤,但我个人只是查询表的all_tab_columns(按column_id排序)并复制/粘贴到我的脚本/过程中。但可能是其他一些避免这种情况的原因,不确定。

#1