13 Replies Latest reply: Apr 25, 2012 5:33 AM by Etbin RSS

    Loop statement in oracle

    932166
      SOurce Table
      ID ADDRESS1 ADDRESS2
      11 12 QA ST. 16 SELI ST.
      12 455 LANE 3222 ROGER LANE


      Target Table:
      ID TYPE ADDRESS1 ADDRESS2
      11 NORMAL XYZ st.
      11 HIGH ABC st.
      12 NORMAL TYU st.
      12 HIGH XCV st.

      Description of Problem:

      I have a source table in which ADDRESS1 pertains to Address1 column in target table where type= 'NORMAL' and ADDRESS2 pertains to Address 2 column in target table where type = 'HIGH'
      I want to update the target table using source table on ID so that Address column in target table is updated corresponding to the ADDRESS 1 AND ADDRESS 2 columns from source table.

      I am using the following code:

      MERGE
      INTO TARGET tgt
      USING SOURCE src
      ON (src.ID = tgt.ID)
      WHEN MATCHED
      THEN
      UPDATE
      SET tgt.address1 = src.address1
      where TGT.type = '=NORMAL'

      How can i update the address2 column in the target table where type = 'HIGH'
      in one shot.
      I dont want to write two seprate queries.
      I think i can use loop statement.
      Please Advice as how to use loop, else any other suggestion.

      Thanks
        • 1. Re: Loop statement in oracle
          Tubby
          user11018028 wrote:
          SOurce Table
          ID ADDRESS1 ADDRESS2
          11 12 QA ST. 16 SELI ST.
          12 455 LANE 3222 ROGER LANE


          Target Table:
          ID TYPE ADDRESS1 ADDRESS2
          11 NORMAL XYZ st.
          11 HIGH ABC st.
          12 NORMAL TYU st.
          12 HIGH XCV st.

          Description of Problem:

          I have a source table in which ADDRESS1 pertains to Address1 column in target table where type= 'NORMAL' and ADDRESS2 pertains to Address 2 column in target table where type = 'HIGH'
          I want to update the target table using source table on ID so that Address column in target table is updated corresponding to the ADDRESS 1 AND ADDRESS 2 columns from source table.

          I am using the following code:

          MERGE
          INTO TARGET tgt
          USING SOURCE src
          ON (src.ID = tgt.ID)
          WHEN MATCHED
          THEN
          UPDATE
          SET tgt.address1 = src.address1
          where TGT.type = '=NORMAL'

          How can i update the address2 column in the target table where type = 'HIGH'
          in one shot.
          I dont want to write two seprate queries.
          I think i can use loop statement.
          Please Advice as how to use loop, else any other suggestion.

          Thanks
          Maybe this? If you provide scripts for create tables and sample data this wouldn't be a guess :)
          MERGE
          INTO TARGET tgt
          USING SOURCE src
          ON (src.ID = tgt.ID)
          WHEN MATCHED
          THEN
          UPDATE
          SET tgt.address1 = case when TGT.type  = 'NORMAL' then src.address1 else tgt.address1 end
          ,   tgt.address2 = case when TGT.type != 'NORMAL' then src.address2 else tgt.address2 end
          • 2. Re: Loop statement in oracle
            932166
            Thanks. it really hepls.
            How can we use the case statement in case insert in the same merge statement.
            Please Advice
            • 3. Re: Loop statement in oracle
              932166
              I want to use the insert statement to insert new records (if any) to the target table.


              'WHEN NOT MATCHED'
              • 4. Re: Loop statement in oracle
                Tubby
                user11018028 wrote:
                I want to use the insert statement to insert new records (if any) to the target table.
                'WHEN NOT MATCHED'
                Don't think that's going to work well given the data model you're constrained to.

                You'd be better off running a MERGE (or UPDATE) statement to update existing records as needed, and running a separate process for the inserts.
                • 5. Re: Loop statement in oracle
                  932166
                  A loop statement wont be of any help?
                  • 6. Re: Loop statement in oracle
                    Tubby
                    user11018028 wrote:
                    A loop statement wont be of any help?
                    Definitely not.

                    If you can do something in a single SQL statement then you should. In this case you'd have 2 SQL statements, but that's still going to perform much better than any sort of looping construct you can come up with.

                    Something i just thought of that would probably work "best" would be to make your source table look more like your target.
                    select ID, ADDRESS1, 'NORMAL'
                    from source_table
                    union all
                    select ID, ADDRESS2, 'HIGH'
                    from source_table
                    And then use that in your MERGE statement, should allow for both inserting and updating.

                    Edited by: Tubby on Apr 24, 2012 12:53 PM
                    • 7. Re: Loop statement in oracle
                      Etbin
                      Maybe
                      MERGE INTO TARGET tgt
                      USING (select id,'NORMAL' type,address1 address
                               from SOURCE src
                             union all
                             select id,'HIGH',address2
                               from SOURCE src
                            )
                         ON (src.ID = tgt.ID
                        AND  src.type = tgt.type
                            )
                      WHEN MATCHED
                      THEN UPDATE
                              SET tgt.address1 = decode(TGT.type,'NORMAL',src.address,tgt.address1),
                                  tgt.address2 = decode(TGT.type,'HIGH',src.address,tgt.address2)
                      WHEN NOT MATCHED
                      THEN INSERT (id,type,address1,address2)
                           VALUES (src.id,src.type,decode(src.type,'NORMAL',src.address),decode(src.type,'HIGH',src.address))
                      Regards

                      Etbin

                      Edited by: Etbin on 24.4.2012 23:04
                      Sorry, should have checked before posting
                      • 8. Re: Loop statement in oracle
                        932166
                        I am sorry guys, Actually, Address 1 in the source tables is linked to (pertains to) target table where type = 'NORMAL'
                        AND Address 2 where type = 'HIGH'

                        Initially i was mapppinf Address 1 in source table to Address 1 in target table and

                        Address 2 in source table to Address 2 in target table.

                        Please Advice. Apologize for the mistake.
                        Thanks
                        • 9. Re: Loop statement in oracle
                          Etbin
                          maybe (guessing - no need for tgt.address2 ?)
                          MERGE INTO TARGET tgt
                          USING (select id,'NORMAL' type,address1 address
                                   from SOURCE src
                                 union all
                                 select id,'HIGH',address2
                                   from SOURCE src
                                )
                             ON (src.ID = tgt.ID
                            AND  src.type = tgt.type
                                )
                          WHEN MATCHED
                          THEN UPDATE
                                  SET tgt.address1 = src.address
                          WHEN NOT MATCHED
                          THEN INSERT (id,type,address1)
                               VALUES (src.id,src.type,src.address)
                          Regards

                          Etbin
                          • 10. Re: Loop statement in oracle
                            932166
                            This is what i wrote:


                            MERGE
                            NTO target tgt
                            USING source src
                            ON (src.id = tgt.id)
                            WHEN MATCHED
                            THEN
                            UPDATE
                            SET tgt.address1 = CASE when tgt.type = 'Normal' then src.Address1
                            when tgt.type = 'High' then src.Address2 else tgt.address1 end,
                            tgt.address2 = CASE when tgt.type = 'Normal' then src.Address1
                            when tgt.type = 'High' then src.Address2 else tgt.address2 end
                            WHEN NOT MATCHED
                            THEN
                            INSERT (tgt.Address_Type,
                            tgt.address1 ,
                            tgt.address2
                            )
                            VALUES ('Normal',
                            src.address1,
                            Null)

                            I am not able to work around with insert statement. In the above statement, i also want to insert one more row with type = 'High' like:

                            INSERT (tgt.Address_Type,
                            tgt.address1 ,
                            tgt.address2
                            )
                            VALUES ('High',
                            Null,
                            src.address2)

                            Please check the above code and let me know how to refine it.

                            Thanks
                            • 11. Re: Loop statement in oracle
                              Etbin
                              If you want to do insert too you must use Tubby's suggestion but then I'm not sure about the update
                              MERGE INTO TARGET tgt
                              USING (select id,'NORMAL' type,address1 address
                                       from SOURCE src
                                     union all
                                     select id,'HIGH',address2
                                       from SOURCE src
                                    )
                                 ON (src.ID = tgt.ID
                                AND  src.type = tgt.type
                                    )
                              WHEN MATCHED
                              THEN UPDATE
                                      SET tgt.address1 = case when TGT.type = 'NORMAL' then src.address else tgt.address1 end,
                                          tgt.address2 = case when TGT.type = 'HIGH' then src.address else tgt.address2 end
                              WHEN NOT MATCHED
                              THEN INSERT (id,type,address1,address2)
                                   VALUES (src.id,src.type,decode(src.type,'NORMAL',src.address),decode(src.type,'HIGH',src.address))
                              Regards

                              Etbin
                              • 12. Re: Loop statement in oracle
                                Paulie
                                user11018028 wrote:
                                This is what i wrote:
                                MERGE
                                ..
                                Please check the above code and let me know how to refine it.
                                It would be better if you wrote

                                CREATE TABLE Billy
                                (
                                field1 type1,
                                field2 type2,
                                ..
                                ..
                                fieldN typeN
                                );

                                INSERT INTO TABLE Billy VALUES(Value1, Value2, .., .., ValueN);
                                INSERT INTO TABLE Billy VALUES(Value1, Value2, .., .., ValueN);
                                ..
                                ..
                                INSERT INTO TABLE Billy VALUES(Value1, Value2, .., .., ValueN);

                                And then explain clearly the result you want.

                                Paul...
                                • 13. Re: Loop statement in oracle
                                  Etbin
                                  This works for me - might be not the way you want
                                  MERGE INTO TARGET tgt
                                  USING (select "ID",'NORMAL' "TYPE",address1 address
                                           from SOURCE
                                         union all
                                         select "ID",'HIGH',address2
                                           from SOURCE
                                        ) src
                                     ON (src."ID" = tgt."ID"
                                    AND  src."TYPE" = tgt."TYPE"
                                        )
                                  WHEN MATCHED
                                  THEN UPDATE
                                          SET tgt.address1 = case when TGT."TYPE" = 'NORMAL' then src.address else tgt.address1 end,
                                              tgt.address2 = case when TGT."TYPE" = 'HIGH' then src.address else tgt.address2 end
                                  WHEN NOT MATCHED
                                  THEN INSERT ("ID","TYPE",address1,address2)
                                       VALUES (src."ID",src."TYPE",decode(src."TYPE",'NORMAL',src.address),decode(src."TYPE",'HIGH',src.address))
                                  Regards

                                  Etbin

                                  Edited by: Etbin on 25.4.2012 12:32
                                  SOURCE:
                                  
                                  ID | ADDRESS1  | ADDRESS2
                                  ===|===========|================
                                  11 | 12 QA ST. | 16 SELI ST.
                                  12 | 455 LANE  | 3222 ROGER LANE
                                  
                                  TARGET:
                                  
                                  ID | TYPE   | ADDRESS1  | ADDRESS2
                                  ===|========|===========|================
                                  11 | NORMAL | XYZ st.   | 
                                  11 | HIGH   | ABC st.   | 
                                  12 | NORMAL | TYU st.   | 
                                  12 | HIGH   | XCV st.   | 
                                  
                                  AFTER MERGE:
                                  
                                  ID | TYPE   | ADDRESS1  | ADDRESS2
                                  ===|========|===========|================
                                  11 | NORMAL | 12 QA ST. | 
                                  11 | HIGH   | ABC st.   | 16 SELI ST.
                                  12 | NORMAL | 455 LANE  | 
                                  12 | HIGH   | XCV st.   | 3222 ROGER LANE