This discussion is archived
8 Replies Latest reply: Feb 23, 2013 11:44 AM by Frank Kulash RSS

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

ti3r Newbie
Currently Being Moderated
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}
  • 1. Re: Need to update multiple rows in one table from multiple rows in another
    user7347338 Newbie
    Currently Being Moderated
    (Removed, used wrong userid)
  • 2. Re: Need to update multiple rows in one table from multiple rows in another
    Stew Ashton Expert
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points