CODEWITHSUNDEEP
sqloracle-databaseplsqlinsert
David Oneill
David Oneill

Reputation: 13115

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 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 name and col1 are the copied, and col2 is a.

Upvotes: 16

Views: 46375

Answers (3)

Kenny Hung
Kenny Hung

Reputation: 442

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.

Upvotes: 2

DCookie
DCookie

Reputation: 43533

Will this do it?

INSERT INTO yourtable
       (SELECT name, col1, 'a'
          FROM yourtable 
         WHERE col2 is NULL);

Upvotes: 3

OMG Ponies
OMG Ponies

Reputation: 332731

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.

Upvotes: 21

Related Questions