8 Replies Latest reply: May 9, 2012 6:44 AM by Oracle_Walker RSS

    Merge only one row out of multiple rows

    Oracle_Walker
      I need to prepare a merge statement which merge only one row in to a table. Below is the table definition, sample data and merge statement i used.
            CREATE TABLE Mob_Attr (NAME VARCHAR2(20), ATTR_1 VARCHAR2(20), ATTR_2 VARCHAR2(20), ATTR_3 VARCHAR2(20));
            CREATE TABLE Mob_Desc (NAME VARCHAR2(50), ATTR_1 VARCHAR2(20), ID NUMBER(20));      
            CREATE TABLE Mob_Main (NAME VARCHAR2(20), ATTR VARCHAR2(20), ID VARCHAR2(10));     
            
            insert into Mob_Attr(name, attr_1, attr_2,attr_3) values ('n1','aa',null,null);
            insert into Mob_Attr(name, attr_1, attr_2,attr_3) values ('n2','bb',null,null);
            insert into Mob_Attr(name, attr_1, attr_2,attr_3) values ('n3','cc',null,null);
            insert into Mob_Attr(name, attr_1, attr_2,attr_3) values ('n4','dd',null,null);
            
            insert into Mob_Desc (NAME,ATTR_1,ID) values ('n1','aa',10);
            insert into Mob_Desc (NAME,ATTR_1,ID) values ('n1','aa',15);
            insert into Mob_Desc (NAME,ATTR_1,ID) values ('n1','aa',123);
            insert into Mob_Desc (NAME,ATTR_1,ID) values ('n1','aa',1455);
            insert into Mob_Desc (NAME,ATTR_1,ID) values ('n2','bb',455);
            insert into Mob_Desc (NAME,ATTR_1,ID) values ('n2','bb',135);
            insert into Mob_Desc (NAME,ATTR_1,ID) values ('n3','cc',15);
            insert into Mob_Desc (NAME,ATTR_1,ID) values ('n3','cc',52);
            insert into Mob_Desc (NAME,ATTR_1,ID) values ('n4','dd',1455);
            
            merge into Mob_Main ma using
            (select d.name, d.attr_1,d.id from Mob_Attr a, Mob_Desc d
            where a.name = d.name and a.attr_1 = d.attr_1
            ) q on (ma.name=q.name and ma.attr=q.attr_1)
            when not matched then
            insert (NAME,ATTR,ID)
            values (q.name,q.attr_1,q.ID);
            
      Above merge statement will actually merge entire data . But i want the first row will only get inserted to the base table (Mob_Main). If we take name 'n1' first row from table Mob_Desc with ID as '10' only should get inserted. I can go for a cursor to do this as i have huge data volume i prefer merge instead.
        • 1. Re: Merge only one row out of multiple rows
          Igor.M
          MERGE INTO Mob_Main ma
               USING (SELECT d.name, d.attr_1, d.id
                        FROM Mob_Attr a, Mob_Desc d
                       WHERE a.name = d.name AND a.attr_1 = d.attr_1 AND id = 10
                      UNION
                      SELECT d.name, d.attr_1, d.id
                        FROM Mob_Attr a, Mob_Desc d
                       WHERE a.name = d.name AND a.attr_1 = d.attr_1 AND id != 10
                             AND NOT EXISTS
                                        (SELECT 1
                                           FROM Mob_Desc d1
                                          WHERE     d.name = d1.name
                                                AND d.attr_1 = d1.attr_1
                                                AND d1.id = 10)) q
                  ON (ma.name = q.name AND ma.attr = q.attr_1)
          WHEN NOT MATCHED
          THEN
             INSERT     (NAME, ATTR, ID)
                 VALUES (q.name, q.attr_1, q.ID);
          
          COMMIT;
          
          select *
          from Mob_Main
          order by name, id;
          
          NAME                 ATTR                 ID        
          -------------------- -------------------- ----------
          n1                   aa                   10        
          n2                   bb                   135       
          n2                   bb                   455       
          n3                   cc                   15        
          n3                   cc                   52        
          n4                   dd                   1455      
          
          6 rows selected.
          • 2. Re: Merge only one row out of multiple rows
            Oracle_Walker
            Igor,

            Thanks for your help. you merge will actually works for id = 10 but it will cant be constant all the time. In the same example if you see name 'n3' it has the id value as 52 and 15. Basically merge should insert a single row for a name and attr combination and it should be the first retrieved record.
            • 3. Re: Merge only one row out of multiple rows
              Frank Kulash
              Hi,

              Here's one way:
              INSERT INTO mob_main (name, attr, id)
              select  d.name
              ,     d.attr_1
              ,     d.id
              from          Mob_Attr a
              join           Mob_Desc d     on      a.name       = d.name 
                                    and      a.attr_1 = d.attr_1
              left outer join Mob_Main m     on     m.name      = d.name
                                           and     m.attr      = d.attr_1
              where     m.name     IS NULL
              and     ROWNUM     = 1
              ;
              If you really want to use MERGE, then put the 3-way join above in the USING clause.
              • 4. Re: Merge only one row out of multiple rows
                Oracle_Walker
                Frank,

                Insert statement which you have will only insert one row to the base table. But i want to perform a bulk insert, so i prefer to use merge. Also please advice using the rownum is a good practice for this.
                • 5. Re: Merge only one row out of multiple rows
                  Igor.M
                  MERGE INTO Mob_Main ma
                       USING (
                                SELECT d.name, d.attr_1, min(d.id) id
                                FROM Mob_Attr a, Mob_Desc d
                                WHERE a.name = d.name AND a.attr_1 = d.attr_1
                                group by d.name, d.attr_1
                               ) q
                          ON (ma.name = q.name AND ma.attr = q.attr_1)
                  WHEN NOT MATCHED
                  THEN
                     INSERT     (NAME, ATTR, ID)
                         VALUES (q.name, q.attr_1, q.ID);
                  COMMIT;
                   
                  select *
                  from Mob_Main
                  order by name, id;
                  
                  
                  NAME                 ATTR                 ID        
                  -------------------- -------------------- ----------
                  n1                   aa                   10        
                  n2                   bb                   135       
                  n3                   cc                   15        
                  n4                   dd                   1455      
                  
                  4 rows selected.
                  • 6. Re: Merge only one row out of multiple rows
                    Oracle_Walker
                    Igor,

                    You are using the min function to get the group by results. But what can i do if the ID is column is of VARCHAR type. Also i need the first available to be inserted not the min value record. Thanks a lot for your help.
                    • 7. Re: Merge only one row out of multiple rows
                      Igor.M
                      MERGE INTO Mob_Main ma
                           USING (
                                    SELECT distinct d.name, d.attr_1, first_value(d.id) over (partition by d.name, d.attr_1 order by d.rowid) id
                                    FROM Mob_Attr a, Mob_Desc d
                                    WHERE a.name = d.name AND a.attr_1 = d.attr_1
                                   ) q
                              ON (ma.name = q.name AND ma.attr = q.attr_1)
                      WHEN NOT MATCHED
                      THEN
                         INSERT     (NAME, ATTR, ID)
                             VALUES (q.name, q.attr_1, q.ID);
                      COMMIT;
                      
                      select *
                      from Mob_Main
                      order by name, id;
                      
                      NAME                 ATTR                 ID        
                      -------------------- -------------------- ----------
                      n1                   aa                   10        
                      n2                   bb                   455       
                      n3                   cc                   15        
                      n4                   dd                   1455      
                      
                      4 rows selected.
                      • 8. Re: Merge only one row out of multiple rows
                        Oracle_Walker
                        Thank you Igor. Perfect query. Is it adviced to use rowid?