Results 1 to 8 of 8

Thread: Merge Query

  1. #1
    Join Date
    Feb 2009
    Posts
    4

    Unhappy Unanswered: Merge Query

    Hi All,

    My job has source and target tables. The problem is Suppose today i will load 10 records from my source table to my target table. Tommorrow if the same record not available in source table but available in target table,i have to fetch those records using Merge Command and i have to update my one of the target table column 'STAT_C value as 'D'. I don't know how to fetch this records using merge statement. Please help me to resolve this issue.

  2. #2
    Join Date
    Feb 2009
    Posts
    1
    Upon my understanding about ur problem, if the same record is coming to source again and u want to look for such loaded records and update a particular column, thn below mentioned query would work. This will also insert the records if it is not present in target table. Hope this will work for u.

    MERGE INTO Target_table A
    USING Source_table B
    ON
    A.key = B.key
    WHEN MATCHED THEN
    SET A.STAT_C='D'
    ElSE
    INSERT

  3. #3
    Join Date
    Feb 2009
    Posts
    4
    Thanks your reply divzz. I think i have not explained my probem clearly. Today i am loading 100 records from my source table to target table. Tomorrow, If the same 100 records not available in the source table again, but the same record 100 records available in target tables then i have to capture those 100 missed records and update in target table particular column in STAT_C='D'. plz help me to resolve this issue

  4. #4
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    Do you have an example of what you have tried ?
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  5. #5
    Join Date
    Feb 2009
    Posts
    4
    Day 1:

    Source table Target table

    Name / Name Stat_C

    John John I
    Peter Peter I

    Day2:

    Source table Target table

    Name / Name Stat_c

    John D
    Peter D
    Alan Alan I

    I have to capture only the records deleted in source but that deleted records available in target and set Stat_C column as 'D' means Deleted .
    How to i do? i am really struggling on this. plz help me to resolve this issue

  6. #6
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    I am sure someone will be posting a better solution ...

    But, here is mine :

    Code:
    create table sourcet(i int,j int,k int)  ;
    create table targett(i int,j int,k int,status char(1)) ; 
     
     delete from targett ; 
     
    delete from sourcet ; 
    -- (0,0,0) has been deleted from sourcet
    -- (1,1,1)  is a new value in sourcet
    -- (3,3,4) in source has now been changed to (3,3,3)
     
     
    insert into targett(i,j,k) values (2,2,2),(3,3,4) ,(0,0,0) ; 
    insert into sourcet values (2,2,2),(3,3,3) ,(1,1,1) ; 
     
    merge into targett t using 
    (
      select coalesce(ai,bi)  , aj , ak ,case when ai is null then 'D' when bi is null then 'I' else 'U' end   from(
     select a.i as ai,b.i bi,a.j aj,b.j bj,a.k ak ,b.k bk
        from targett b full outer join sourcet a
          on a.i=b.i 
     ) as x   
    ) s (i,aj,ak,st) on s.i=t.i
    when matched then update set (j,k,status)=(coalesce(s.aj,t.j),coalesce(s.ak,t.k),s.st)
    when not matched then insert (i,j,k,status) values(s.i,aj,ak,st) 
    ;
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  7. #7
    Join Date
    Feb 2009
    Posts
    4
    Hi Sathya,

    Thanks for your kindly reply. when i tried to execute your query, i got the error like this
    (21S02(-158)[IBM][CLI Driver][DB2/AIX64] SQL0158N The number of columns specified for "S" is not the same as the number of columns in the result table. SQLSTATE=42811
    (0.28 secs)

    Query:
    merge into targett t using
    (
    select coalesce(ai,bi) , aj , ak ,case when ai is null then 'D' when bi is null then 'I' else 'U' end from(
    select a.i as ai,b.i bi,a.j aj,b.j bj,a.k ak ,b.k bk
    from targett b full outer join sourcet a
    on a.i=b.i
    ) as x
    ) s (i,aj,ak,st) on s.i=t.i
    when matched then update set (j,k,status)=(coalesce(s.aj,t.j),coalesce(s.ak,t.k ),s.st)
    when not matched then insert (i,j,k,status) values(s.i,aj,ak,st)
    ;

    what does S Alias and t Alias do? what does this query do :

    ----- s (i,aj,ak,st) on s.i=t.i. -----

    I am not understand this part. Could you please explain about this.

  8. #8
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    s alias is the USING part of the MERGE Statement.
    t alias is the target table of the MERGE Statement.

    Have a look at the MERGE syntax and compare the query with it ..

    BTW, the statements work for me ... Not sure why your problme is
    Visit the new-look IDUG Website , register to gain access to the excellent content.

Posting Permissions

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