8 Replies Latest reply: Feb 23, 2013 1:44 PM by Frank Kulash RSS

    Need to update multiple rows in one table from multiple rows in another

    ti3r
      I am coming to conclusion that I can't do this because of the lack of a related unique key, but maybe you can help me with a solution. I have tried all of the solutions posted here but they do not work in my scenario. I have codes in one table that is a list of codes with related market that they are to be used for. I have a second table of customers that do not have the code assigned to them until they are sent an advertisement. That table has the market_id for each customer and the code table has the market_id associated with each code. I want to update the customer table with any one of the mergecodes associated with the market_id - but between the two tables thee is not a unique key to relate. I thought I could just update records that had matching market_ids, but nothing works. From the two tables below I want to be able to update t3tm.mergecode with any one of the mergecodes that has the same market_id ('Austin") in t3mc. it does not matter which one is associated with a particular customer.

      {code}

      create table T3mc (mergecode varchar(10), market_id varchar(25), week number, used varchar2(5));

      Insert into T3mc values ('A8976', 'Austin', 2, '');
      Insert into T3mc values ('A8988', 'Austin', 2, '');
      Insert into T3mc values ('A9900', 'Houston', 2, '');
      commit;

      create table T3tm (last_name varchar(20), market_id varchar(25), mergecode varchar(10), id number);

      insert into T3tm Values ('Smith', 'Austin', '', 1);
      insert into T3tm Values ('Jones', 'Austin', '', 2);
      commit;
      {code}

      {code}
      Select * from T3mc;
      MARKET_ID, WEEK, USED, MERGECODE
      Austin 2 (null) A8976
      Austin 2 (null) A8988
      Houston 3 (null) A9900
      Select * from T3tm;

      LAST_NAME, MARKET_ID, MERGECODE ID
      Smith Austin (null) 1
      Jones Austin (null) 2
      {code}
        • 2. Re: Need to update multiple rows in one table from multiple rows in another
          Stew Ashton
          It's too late for me to test this, but here's the idea. Use MERGE.
          merge into T3tm o
          using (select market_id, min(mergecode) mergecode from T3mc) n
          on (o.market_id = n.market_id and decode(o.mergecode,n.mergecode,0,1) = 1)
          when matched then update set mergecode = n.mergecode;
          The "n" subquery can be anything you want as long as it returns exactly one row per market_id. The DECODE is a way of comparing mergecodes even if one of them is null: this avoids doing an update on rows that are already OK.
          • 3. Re: Need to update multiple rows in one table from multiple rows in another
            Solomon Yakobson
            ti3r wrote:
            From the two tables below I want to be able to update t3tm.mergecode with any one of the mergecodes that has the same market_id ('Austin") in t3mc. it does not matter which one is associated with a particular customer.
            {code}
            update T3tm a
            set mergecode = (
            select mergecode
            from T3mc b
            where b.market_id = a.market_id
            and rownum = 1
            )
            where market_id in (
            select market_id
            from T3mc
            )
            /

            2 rows updated.

            SQL> select *
            2 from T3tm
            3 /

            LAST_NAME MARKET_ID MERGECODE ID
            -------------------- ------------------------- ---------- ----------
            Smith Austin A8976 1
            Jones Austin A8976 2

            SQL>
            {code}

            SY.
            • 4. Re: Need to update multiple rows in one table from multiple rows in another
              ti3r
              Thank you - I can actually enjoy the weekend now! I am going to also look into the merge command - I have not worked at all with that before. Thanks for the speedy responses late on a Friday!
              • 5. Re: Need to update multiple rows in one table from multiple rows in another
                ti3r
                I might have gotten excited too soon. This puts the same mergecode in both customer records. They need to be different.
                • 6. Re: Need to update multiple rows in one table from multiple rows in another
                  Frank Kulash
                  Hi,
                  ti3r wrote:
                  I might have gotten excited too soon. This puts the same mergecode in both customer records. They need to be different.
                  That may not be possible. If there are more cusomers that need mergecodes than there are mergecodes in that market_id, then some mergecodes will have to be re-used.
                  The following keeps re-use to a minimum; that is, no mergecode will be assigned N+1 (or more) times until every mergecode in that market_id has been assigned N times.
                  MERGE INTO t3tm          dst
                  USING   (
                            WITH     markets          AS
                            (
                                 SELECT     mergecode
                                 ,     market_id
                                 ,     COUNT (*)     OVER ( PARTITION BY  market_id ) AS m_cnt
                                 ,     ROW_NUMBER () OVER ( PARTITION BY  market_id
                                                           ORDER BY          mergecode
                                                   )                      AS m_num
                                 FROM    t3mc
                            )
                            ,     customers     AS
                            (
                                 SELECT  last_name
                                 ,     market_id
                                 ,     COUNT (*)     OVER ( PARTITION BY  market_id ) AS c_cnt
                                 ,     ROW_NUMBER () OVER ( PARTITION BY  market_id
                                                           ORDER BY          last_name
                                                   )                      AS c_num
                                 FROM    t3tm
                                 WHERE     mergecode     IS NULL
                            )
                            SELECT  c.last_name
                            ,     m.mergecode
                            FROM     markets        m
                            JOIN     customers  c  ON   c.market_id  = m.market_id
                                            AND  MOD ( m.m_num
                                                        , LEAST (m.m_cnt, c.c_cnt)
                                                  )        = MOD ( c.c_num
                                                                  , LEAST (m.m_cnt, c.c_cnt)
                                                           )
                       )          src
                  ON     (dst.last_name     = src.last_name)
                  WHEN MATCHED THEN UPDATE
                  SET     dst.mergecode  = src.mergecode
                  ;
                  It doesn't matter if there are more customers or mergecodes in the matket_id, or if the numbers happen to be the same; this statement will work in any case.
                  • 7. Re: Need to update multiple rows in one table from multiple rows in another
                    ti3r
                    This worked perfectly - and I would have never figured it out on my own - very much appreciated. I tried to mark the message as correct but there is no option. Let me know if I can rate it or mark it somehow.

                    Edited by: ti3r on Feb 23, 2013 10:50 AM
                    • 8. Re: Need to update multiple rows in one table from multiple rows in another
                      Frank Kulash
                      Hi,

                      You can only mark 1 reply per thread as "Correct", but you can mark up to 5 as "Helpful". You should see a "Helpful" button before the "Reply" button at the top right of every reply.
                      The main thing is to mark the thread as "Answered", which you've already done.