SQL Oracle：在更新一个字段时复制行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3095998/
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
Oracle: copy row while updating one field
提问by David Oneill
Please note: I amasking the question I want answered. I know this question means the database is set up poorly. So I will vote down any answers that suggest changing the way the table is set up.
请注意：我在问我想要回答的问题。我知道这个问题意味着数据库设置不佳。所以我会否决任何建议改变表格设置方式的答案。
I need to duplicate a bunch of rows, while changing one value.
我需要复制一堆行，同时更改一个值。
name col1 col2
dave a nil
sue b nil
sam c 5
needs to become:
需要变成：
name col1 col2
dave a nil
dave a a
sue b nil
sue b a
same c 5
IE for all entries in this table where col2 is null, create a new entry in the table where nameand col1are the copied, and col2is a.
IE 对于此表中的所有条目 where col2 is null，在表中创建一个新条目，其中name和col1是复制的，并且col2是a。
回答by OMG Ponies
Use:
用：
INSERT INTO table
(name, col1, col2)
SELECT t.name, t.col1, 'a'
FROM TABLE t
WHERE t.col2 IS NULL
That's assuming neither the nameor col1columns are a primary key or have a unique constraint on either.
那是假设name或col1列既不是主键，也不是对任何一个都有唯一约束。
回答by DCookie
Will this do it?
这会做吗？
INSERT INTO yourtable
(SELECT name, col1, 'a'
FROM yourtable
WHERE col2 is NULL);
回答by Kenny Hung
If the number of columns is large, you could copy the data you want into a temporary table, alter the data in the temporary table as you wanted, then copy the contents of the temporary table back into the original, and delete the temporary table.
如果列数较多，可以将需要的数据复制到临时表中，根据需要修改临时表中的数据，然后将临时表的内容复制回原表，删除临时表。
