This discussion is archived
2 Replies Latest reply: Dec 7, 2012 7:50 AM by BluShadow RSS

Creating test data

970021 Newbie
Currently Being Moderated
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 Guru Moderator
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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".

Legend

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