2 Replies Latest reply: Dec 7, 2012 9:50 AM by BluShadow RSS

    Creating test data

    970021
      I have the two tables below. There is no correlation between the two tables. There is nothing I can join them on.
      create table t as
      select 'GS' col1, 123214 varnum from dual union all
      select 'GE' col1, 563455 varnum from dual union all
      select 'WE' col1, 982342 varnum from dual union all
      select 'HI' col1, 331212 varnum from dual;
      create table output as
      select 'HUW' pk_col, 654532 varnum from dual union all
      select 'OHD' pk_col, 268894 varnum from dual union all
      select 'PEW' pk_col, 779835 varnum from dual union all
      select 'NCS' pk_col, 879875 varnum from dual union all
      select 'ITE' PK_col, 992732 varnum from dual;
      I would like to update table t above with values on varnum from table OUTPUT so the outcome is as follow:
      with t as (
      select 'GS' col1, 654532 varnum from dual union all
      select 'GE' col1, 268894 varnum from dual union all
      select 'WE' col1, 779835 varnum from dual union all
      select 'HI' col1, 879875 varnum from dual)
      select * From t;
      I don't need any specific order of how the values get updated. But I basically need t.varnum to match output.varnum. Please help
        • 1. Re: Creating test data
          BluShadow
          In that case you need to manufacture a relation between them.
          Do that by assigning a row number to each row in each table e.g. using "row_number() over (order by 1) as rn", and then joining on "rn" when updating the one table with the other.

          Have a go and see how you get on.
          • 2. Re: Creating test data
            Stew Ashton
            merge into t
            using (
              select rid, varnum from (
                select rowid rid, rownum rn from t
              ) join (
                select varnum, rownum rn from output
              ) using (rn)
            ) n
            on (t.rowid = n.rid)
            when matched then update set varnum = n.varnum;
            P.S. Sorry BluShadow, I already wrote the code before you said "try it yourself" so I'm posting it anyway.

            P.P.S. I use rownum instead of row_number() because the OP said "I don't care about order".