OraFAQ Forum: SQL & PL/SQL » duplicating records

Home » SQL & PL/SQL » SQL & PL/SQL » duplicating records

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

duplicating records [message #23485]

Thu, 12 December 2002 09:39

D.
Messages: 5
Registered: November 2001

Junior Member

How to make a copy of record in the same table changing only PK (getting it from sequence).. ?
I don't want to enumerate each column in table !

the statement should look like this (more or less ):
insert into table1 (select * (but new ID) from table1 where id=xyz)

Report message to a moderator

Re: duplicating records [message #23486 is a reply to message #23485]

Thu, 12 December 2002 09:55

Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

SQL> select * from dept;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

-- you can also use a sequence instead of rownum.
-- like deptno+myseq.nextval or deptno||myseq.nextval
SQL> ed
Wrote file afiedt.buf

  1  insert into dept
  2* (select deptno+rownum,dname,loc from dept)
SQL> /

4 rows created.

SQL> select * from dept;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON
        11 ACCOUNTING     NEW YORK
        22 RESEARCH       DALLAS
        33 SALES          CHICAGO
        44 OPERATIONS     BOSTON

8 rows selected.

SQL>

Report message to a moderator

Re: duplicating records [message #23505 is a reply to message #23485]

Fri, 13 December 2002 09:01

sridhar
Messages: 119
Registered: December 2001

Senior Member

First of all, it runs into unique_constraint violation.
By just adding rownum to the pk, does not solve it, here is the example:

SQL> select * from test_121302;

ID NAME
--------- --------------------
1 SriDHAR
2 Mr.D

SQL> insert into test_121302
2 (select id + rownum, name from test_121302)
3 /
insert into test_121302
*
ERROR at line 1:
ORA-00001: unique constraint (APPS.T_PK) violated

SQL> insert into test_121302
2 select a.max_id + rownum, c.* from
3 (select rownum rn, x.id max_id from (select max(id) id from test_121302) x) a,
4 (select rownum rn from test_121302) b, (select name from test_121302) c
5 where a.rn = b.rn
6 /

2 rows created.

SQL> select * from test_121302;

ID NAME
--------- --------------------
1 SriDHAR
2 Mr.D
3 SriDHAR
4 Mr.D

Thx,
Sri

Report message to a moderator

Re: This answers your 2nd Qn - Better approach as well [message #23506 is a reply to message #23485]

Fri, 13 December 2002 10:26

sridhar
Messages: 119
Registered: December 2001

Senior Member

SQL> alter table test_121302 disable constraint t_pk;

Table altered.

SQL> select * from test_121302;

ID NAME
--------- --------------------
1 SriDHAR
2 Mr.D

SQL> insert into test_121302
2 select * from test_121302; -- You are grabbing all columns using *, this is what you want

2 rows created.

SQL> select * from test_121302;

ID NAME
--------- --------------------
1 SriDHAR
2 Mr.D
1 SriDHAR
2 Mr.D

SQL> update test_121302
2 set id = rownum;

4 rows updated.

SQL> select * from test_121302;

ID NAME
--------- --------------------
1 SriDHAR
2 Mr.D
3 SriDHAR
4 Mr.D

SQL> alter table test_121302 enable constraint t_pk;

Table altered.

Thx,
Sri

Report message to a moderator

Previous Topic:	help for trigger
Next Topic:	SQL Question

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu May 16 09:02:14 CDT 2024