#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2006
    Posts
    205
    Rep Power
    15

    Copying multiple columns


    Hi all!

    I'm looking for a more effective way to copy multiple columns from another table where 1-1 type relation exists (by filtering)


    See this example:

    TABLE CURRENT
    ID
    Machine
    Index1_old
    Index1_new
    Index2_old
    Index2_new
    Index3_old
    Index3_new

    TABLE ARCHIVE
    ID
    Month
    Machine
    Index1_old
    Index1_new
    Index2_old
    Index2_new
    Index3_old
    Index3_new

    at every archiving i empty the CURRENT table, insert existing Machines:

    insert into CURRENT (Machine)
    select ID from Machines where Deleted=0;
    and then fill up their old index values from ARCHIVE table, like this:

    UPDATE CURRENT c SET
    Index1_old = (select Index1_new from ARCHIVE where Machine=c.Machine and Month=:"_OLDMONTH"),
    Index2_old = (select Index2_new from ARCHIVE where Machine=c.Machine and Month=:"_OLDMONTH"),
    Index2_old = (select Index2_new from ARCHIVE where Machine=c.Machine and Month=:"_OLDMONTH");
    i guess this kind of update will run 3 queries for each row on CURRENT table


    Do you know an alternative way of doing this?

    The objectives are:

    1. Simpler syntax
    2. Faster/more efficient update
    Last edited by nagysz; September 29th, 2013 at 07:24 AM.
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2006
    Location
    Plovdiv. Bulgaria
    Posts
    226
    Rep Power
    13
    Code:
    execute block (p_old_month integer = @month)
    as
    declare variable index_1 integer;
    declare variable index_1 integer;
    declare variable index_1 integer;
    declare variable machine integer;
    begin
      for select
        a.machine,
        a.index_1_new,
        a.index_2_new,
        a.index_3_new
      from
        archive a
      where
       a.Month=:p_OLD_MONTH
      into
        machine, index_1, index_2, index_3
      do
        update current c set
          index_1_old = :index_1,
          index_2_old = :index_2,
          index_3_old = :index_3
       where
          c,machine = :machine;
    
    end
    Code:
    merge into current c
    using archive a
    on c.machine = a.machine and a.month = :old_month
    when matched then
    update set 
      c.index_old_1 = a.index_1_new,
      c.index_old_2 = a.index_2_new,
      c.index_old_3 = a.index_3_new;
    Code:
    merge into current c
    using (
      select 
        a.machine, 
        a.index_1_old, 
        a.index_2_old, 
        a.index_3_old 
      from 
        archive a 
      where 
         a.month = :old_month) a
    on c.machine = a.machine
    when matched then
    update set 
      c.index_old_1 = a.index_1_new,
      c.index_old_2 = a.index_2_new,
      c.index_old_3 = a.index_3_new;
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2006
    Posts
    205
    Rep Power
    15
    Genial! Thank you so much!

IMN logo majestic logo threadwatch logo seochat tools logo