7 Replies Latest reply: Jun 27, 2013 2:00 AM by Pacmann RSS

    Copy rows of data in the same table

    866532

      Below is the structure of the table I am working I would like to all the value with ID=1 into new rows. The ID should become 2 with the same data.

       

      I am using below query to accomplish, but getting error.

      Insert into hsp_column_detail_item (DETAIL_ID, VALUE, POSITION, GENERATION, OPERATOR, LABEL)

       

      Select 2, VALUE, POSITION, GENERATION, OPERATOR, LABEL

      from

      hsp_column_detail_item

      where

      detail_id =1

       

      ORA-02291: integrity constraint (PLANSAMP.FK_HSP_DETAIL_ID) violated - parent key not found

       

       

      Columns

       

      Detail_ID     YINTEGER    Frequency   
      VALUE     YFLOAT         None   
      POSITION     YINTEGER    None   
      GENERATION     YINTEGER    None   
      OPERATOR     YINTEGER    None   
      LABEL     YVARCHAR2 (1500 Byte)    None       

       

       

      Sample data:

       

      Detail_ID  Value Position  Generation  Operator  label

       

      15000          0               1     abc

       

      13001          0               1     def

       

      12002          0               1     efg

       

       

      Indexes

       

      Index Name           Column Name

       

      Col_Detail_Item      Detail_ID

       

      Col_Detail_Item      Position
        • 1. Re: Copy rows of data in the same table
          JustinCave

          The error you're getting seems pretty clear.  There is a foreign key on this table that is being violated.  Based on the name of the foreign key, my guess is that this is a child table and DETAIL_ID is the foreign key (though that implies that you have no primary key on this table which is almost certainly a mistake in your data model).  The foreign key prevents you from having rows in this child table that have a DETAIL_ID that is not in the parent table and there is no row in the parent table with a DETAIL_ID of 2. 

           

          My guess is that you need to add a row to the parent table where the DETAIL_ID is 2 before you insert the row into the child table.

          • 2. Re: Copy rows of data in the same table
            866532

            Thank you Justin. As you can understand that I am not much familiar with SQL. These tables are system generated and I am trying to add extra rows of data from the backend.

            There are 2 tables HSP_COLUMN_DETAIL and HSP_COLUMN_DETAIL_ITEM in which I would like to add copy rows from the same table.

             

            HSP_COLUMN_DETAIL is the parent table with primary key but I get this error when I try to insert new row in this table

             

            Insert into hsp_column_detail (DETAIL_ID, PLAN_TYPE, DIM1, DIM2, DIM3, DIM4, DIM5, DIM6, DIM7, DIM8, DIM9, DIM10, DIM11, DIM12, DIM13, DIM14, DIM15, DIM16, DIM17, DIM18, DIM19, DIM20)

            select 2, PLAN_TYPE, DIM1, DIM2, DIM3, DIM4, DIM5, DIM6, DIM7, DIM8, DIM9, DIM10, DIM11, DIM12, DIM13, DIM14, DIM15, DIM16, DIM17, DIM18, DIM19, DIM20 from hsp_column_detail

            where

            dim1=(select object_id from hsp_object where object_name='Actual' and object_type=31) and

            dim5=(select object_id from hsp_object where object_name='Working' and object_type=35) and

            dim7=(select object_id from hsp_object where object_name='FY13' and object_type=38) and

            DETAIL_ID = '3'

             

             

            Error at line 1

            ORA-00001: unique constraint (EPM_PLAN_PLANSAMP.PK_HSP_COL_DETAIL) violated

            • 3. Re: Copy rows of data in the same table
              Pacmann

              Hi,

               

              Which is the PK_HSP_COL_DETAIL primary key ? (DETAIL_ID ?)

              You are trying to insert a row for an existing key... check the existing line for this key, then decide you want to use this one for the item table or a new one with another PK...

              • 4. Re: Copy rows of data in the same table
                866532

                Detail_ID is the primary key.  I would like to use Detail_ID for for HSP_COL_DETAIL item.  How can i make it work  ?

                 

                Index Name                   Unique    Logging    Degree    Column Name   Order    Position     Index Owner

                PK_HSP_COL_DETAIL   Y             YES           1          DETAIL_ID       Asc      1              EPM_PLAN_PLANSAMP

                • 5. Re: Copy rows of data in the same table
                  Pacmann

                  Look for the detail_id that suits you in the HSP_COL_DETAIL table, and use this detail_id for the inser in HSP_COL_DETAIL_ITEM.

                  If this line does not exist, INSERT a new line IN HSP_COL_DETAIL with a new detail_id (that is not used yet), and also use this detail_id for the insert in HSP_COL_DETAIL_ITEM.

                  • 6. Re: Copy rows of data in the same table
                    866532

                    Hello Pacmann, Thank you for your help.

                    When I run this I get

                    Insert Into HSP_COLUMN_DETAIL ( DETAIL_ID , PLAN_TYPE , DIM1 , DIM2 , DIM3 , DIM4 , DIM5 , DIM6 ,

                    DIM7 , DIM8 , DIM9 , DIM10 , DIM11 , DIM12 , DIM13 , DIM14 , DIM15 ,

                    DIM16 , DIM17 , DIM18 , DIM19 , DIM20 )

                    Select 5 , PLAN_TYPE , DIM1 , DIM2 , DIM3 , DIM4 , DIM5 , DIM6 ,

                    DIM7 , DIM8 , DIM9 , DIM10 , DIM11 , DIM12 , DIM13 , DIM14 , DIM15 ,

                    DIM16 , DIM17 , DIM18 , DIM19 , DIM20

                    From HSP_COLUMN_DETAIL

                    Where detail_id in

                    (select detail_id from hsp_column_detail where

                    dim1=(select object_id from hsp_object where object_name='Actual' and object_type=31) and

                    dim5=(select object_id from hsp_object where object_name='Working' and object_type=35) and

                    dim7=(select object_id from hsp_object where object_name='FY13' and object_type=38)

                    );

                     

                    Ora-00001: unique constraint (PLANSAMP.PK_HSP_COL_DETAIL) voilated

                     

                    But if use the below then I can create the new rows. Can I not use sub-queries in where statement for insert command ?

                     

                    Insert Into HSP_COLUMN_DETAIL ( DETAIL_ID , PLAN_TYPE , DIM1 , DIM2 , DIM3 , DIM4 , DIM5 , DIM6 ,

                    DIM7 , DIM8 , DIM9 , DIM10 , DIM11 , DIM12 , DIM13 , DIM14 , DIM15 ,

                    DIM16 , DIM17 , DIM18 , DIM19 , DIM20 )

                    Select 5 , PLAN_TYPE , DIM1 , DIM2 , DIM3 , DIM4 , DIM5 , DIM6 ,

                    DIM7 , DIM8 , DIM9 , DIM10 , DIM11 , DIM12 , DIM13 , DIM14 , DIM15 ,

                    DIM16 , DIM17 , DIM18 , DIM19 , DIM20

                    From HSP_COLUMN_DETAIL

                    Where detail_id = 2

                    • 7. Re: Copy rows of data in the same table
                      Pacmann

                      866532 a écrit:

                      Where detail_id in

                      (select detail_id from hsp_column_detail where

                      dim1=(select object_id from hsp_object where object_name='Actual' and object_type=31) and

                      dim5=(select object_id from hsp_object where object_name='Working' and object_type=35) and

                      dim7=(select object_id from hsp_object where object_name='FY13' and object_type=38)

                      );

                       

                      Ora-00001: unique constraint (PLANSAMP.PK_HSP_COL_DETAIL) voilated

                       

                       

                      In order not to violate the unique constraint, two requirements :

                      - The detail_id 5 you are inserting should not already exist in the table

                      - Since you are forcing this detail_id, your subquery should only return one line : so you can write "where detail id = (select...)" and :

                                - either the dim1 / dim5 / dim7 filters are selective enough

                                - or you make some decision on which detail_id to duplicate : max(detail_id) or min(detail_id) or any other way yo select the right / arbitrary one