Oracle: copy row while updating one field
Solution 1
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.
Solution 2
Will this do it?
INSERT INTO yourtable
(SELECT name, col1, 'a'
FROM yourtable
WHERE col2 is NULL);
Solution 3
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.
David Oneill
Updated on February 06, 2020Comments
-
David Oneill about 4 years
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
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 wherename
andcol1
are the copied, andcol2
isa
.