Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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.

like image 947
David Oneill Avatar asked Jun 22 '10 18:06

David Oneill


People also ask

How to copy row in oracle?

To copy or move rows and columns in grids, select the desired rows or columns, and then right-click or click the down arrow in the header, and select either Copy or Move.

How do you update duplicate records in Oracle?

update test_dup set done = 'error' where (acc_num,tel_num, imsi) in (select acc_num, tel_num, imsi from test_dup group by acc_num, tel_num, imsi having count(acc_num) > 1); Then it updates 5 rows i.e. all duplicate rows except non-dups.


1 Answers

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.

like image 86
OMG Ponies Avatar answered Sep 20 '22 18:09

OMG Ponies