SQL 复制同一表中的一行而无需键入 50 多个列名（同时更改 2 列）
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3780383/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Copying a row in the same table without having to type the 50+ column names (while changing 2 columns)
提问by Tolga E
During my job, I usually have to copy rows while changing their primary key and giving them a new stamp and maybe changing the foreign key.
在我的工作中，我通常必须在更改主键的同时复制行并给它们一个新图章，也许还需要更改外键。
The problem is I don't want to type all the column names while doing;
问题是我不想在做的时候输入所有的列名；
insert into table_name
select pk_seq.nextval,
'foreign-key',
col3,
col4...col51
from table_name
where pk_id = "original_primary_key"
And if i do * in the select statement i won't be able to update the first 2 columns...
如果我在 select 语句中做 * 我将无法更新前 2 列...
Is there any way to do how I want to do it?
有什么办法可以做我想做的事吗？
回答by Tony Andrews
Well it may not be much less verbose, but this PL/SQL is an option:
好吧，它可能不会那么冗长，但是这个 PL/SQL 是一个选项：
begin
for r in (select *
from table_name
where pk_id = 'original_primary_key')
loop
r.pk := pk_seq.nextval;
r.fk := 'foreign-key';
insert into table_name values r;
end loop;
end;
回答by Shannon Severance
Based on Tony's answer:
基于托尼的回答：
We know that at most one row will be returned since we are searching on primary key. And assuming that a valid key value is specified, at least one row will be returned. So we don't need the loop:
我们知道最多会返回一行，因为我们正在搜索主键。假设指定了一个有效的键值，至少会返回一行。所以我们不需要循环：
declare
r table_name%ROWTYPE;
begin
select *
into r
from table_name
where pk_id = "original_primary_key";
--
select pk_seq.nextval into r.pk_id from dual;
-- For 11g can use instead: r.pk_id := pk_seq.nextval;
r.fk_id := "new_foreign_key";
insert into table_name values r;
end;
回答by Jeffrey Kemp
You could just query the data dictionary to generate the SQL for you.
您只需查询数据字典即可为您生成 SQL。
SELECT 'tbl.' || column_name || ','
FROM user_tab_columns
WHERE table_name = 'MYTABLE'
ORDER BY column_id;
Get the result of this query, paste into your SQL statement, adapt as necessary, and voila.
获取此查询的结果，粘贴到您的 SQL 语句中，根据需要进行调整，瞧。
回答by OMG Ponies
Sorry - it's an all or nothing affair.
There isn't anything between SELECT *and list the specific columns, it's one or the other.
对不起 - 这是一个全有或全无的事情。列出特定列
之间没有任何内容SELECT *，它是一个或另一个。
回答by TTT
You can create a 'temp' table, update two columns and do an insert-select from this 'temp' table.
您可以创建一个“临时”表，更新两列并从此“临时”表中执行插入选择。
Eaxmple:
例如：
create table temptemp as
select *
from table_name
where pk_id = "original_primary_key"
update temptemp
set col1 = ...
, col2 =....
insert into table2
select * from temptemp;
回答by Jonathan
You could make a simple stored procedure that take a table name and using the data dictionary writes the select statement text for you (the text of the select). Then copy, paste and Modify.
您可以创建一个简单的存储过程，它采用表名并使用数据字典为您编写选择语句文本（选择的文本）。然后复制、粘贴和修改。
