Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2010
    Posts
    64

    Unanswered: Creating duplicate record in table

    Hi

    I have a table with 50 fields and i am trying to create a similar record in the same table. Only one field will vary rest 49 fields will remain same.

    I tried select * into tableA from tableA where name = 'AAA';
    thought of updating one field later. But this is erroring out.

    But i belive this works if you want to create a new record in one table from another table.

    Please help !!

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    But this is erroring out
    Cool. Only if we knew what error you are talking about ..

    If there are no constraints that prevent such an operation, it works just fine:
    Code:
    SQL> create table test as select * from dept;
    
    Table created.
    
    SQL> select * from test order by deptno;
    
        DEPTNO DNAME          LOC
    ---------- -------------- -------------
            10 ACCOUNTING     NEW YORK
            20 RESEARCH       DALLAS
            30 SALES          CHICAGO
            40 OPERATIONS     BOSTON
    
    SQL> insert into test
      2    select * from test
      3    where deptno = 10;
    
    1 row created.
    
    SQL> select * from test order by deptno;
    
        DEPTNO DNAME          LOC
    ---------- -------------- -------------
            10 ACCOUNTING     NEW YORK
            10 ACCOUNTING     NEW YORK
            20 RESEARCH       DALLAS
            30 SALES          CHICAGO
            40 OPERATIONS     BOSTON
    
    SQL>
    But, if there was, for example, an unique index there, it wouldn't work:
    Code:
    SQL> select * from test order by deptno;
    
        DEPTNO DNAME          LOC
    ---------- -------------- -------------
            10 ACCOUNTING     NEW YORK
            20 RESEARCH       DALLAS
            30 SALES          CHICAGO
            40 OPERATIONS     BOSTON
    
    SQL> create unique index ui_test on test (deptno);
    
    Index created.
    
    SQL> insert into test
      2    select * from test
      3    where deptno = 10;
    insert into test
    *
    ERROR at line 1:
    ORA-00001: unique constraint (RADNI.UI_TEST) violated
    
    
    SQL>
    I guess that there are quite a few other erroring possibilities; solution depends on what exactly you have.

    Basically, creating another table (CTAS) and modifying its record and, finally, inserting it into the original table *should* be OK.

  3. #3
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    You can do it like this:
    Code:
    insert into mytable (col1, col2, ..., col50)
    select 'different_value_for_col1', col2, col3, ..., col50
    from mytable
    where ...;
    That is quite verbose; an alternative would be to use some PL/SQL:
    Code:
    begin
      for r in (select *
               from mytable
              where ...)
      loop
        r.col1 := 'new_value_for_col1';
        insert into mytable values r;
      end loop;
    end;

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •