Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2011
    Posts
    1

    Question Unanswered: Help - Column Data Migration

    I have a situation where all the data in a column of Table2 needs to be migrated to another column of Table1 based on a join condition.

    For example,

    Table1 has A,B,C

    and

    Table2 has X,Y,Z. ( Y foreign key to A of Table1)

    I add a new column Z to Table1 and need all the Table2 Z values in Table1 Z column where Table1.A = Table2.Y

    How can I do this in Db2 via SQL, Stored Procs or any other DB2 tools?

    I tried Insert statements such as follows ,. but does not work

    Insert into Table1(Z)
    select Table2.Z from Table1,Table2 where Table1.A = Table2.Y

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Y foreign key to A of Table1
    Then, if multiple rows of Table2 matched with a row of Table1,
    what to do?

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Here is an exmple,
    if multiple rows of Table2 matched with a row of Table1,
    choose one by some additional conditions.

    For example: Take maximum value.
    (Not tested)
    Code:
    MERGE INTO
           table1 t1
     USING (SELECT y
                 , MAX(z) AS z
             FROM  table2
             GROUP BY
                   y
           ) t2
       ON  t2.y = t1.a
    WHEN MATCHED THEN
    UPDATE
       SET z = t2.z
    Last edited by tonkuma; 07-13-11 at 01:44.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •