Oracle:在更新一个字段时复制行 [英] Oracle: copy row while updating one field

问题描述

请注意:我 am 问我要回答的问题.我知道这个问题意味着数据库设置不佳.因此，我将否决所有建议更改表设置方式的答案.

Please note: I am asking 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

需要成为:

name   col1 col2
dave   a    nil
dave   a    a
sue    b    nil
sue    b    a
same   c    5

对于该表中所有位于col2 is null的条目的

IE，在其中复制了name和col1且col2为a的表中创建一个新条目.

IE for all entries in this table where col2 is null, create a new entry in the table where name and col1 are the copied, and col2 is a.

推荐答案

使用:

INSERT INTO table
  (name, col1, col2)
SELECT t.name, t.col1, 'a'
  FROM TABLE t
 WHERE t.col2 IS NULL

假定name或col1列都不是主键，也不是唯一约束.

That's assuming neither the name or col1 columns are a primary key or have a unique constraint on either.

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