Oracle: copy row while updating one field
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 th开发者_高级运维at 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 name
and col1
are the copied, and col2
is a
.
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 name
or col1
columns are a primary key or have a unique constraint on either.
Will this do it?
INSERT INTO yourtable
(SELECT name, col1, 'a'
FROM yourtable
WHERE col2 is NULL);
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.
精彩评论