Merge Query

sureshmini · #1 (**permalink**) 02-12-09, 00:53

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.

divzz · #2 (**permalink**) 02-13-09, 04:31

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

sureshmini · #3 (**permalink**) 02-13-09, 04:53

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

sathyaram_s · #4 (**permalink**) 02-13-09, 06:50

Do you have an example of what you have tried ?

sureshmini · #5 (**permalink**) 02-13-09, 07:21

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

sathyaram_s · #6 (**permalink**) 02-13-09, 08:45

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) 
;

sureshmini · #7 (**permalink**) 02-16-09, 01:21

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.

sathyaram_s · #8 (**permalink**) 02-16-09, 06:15

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

Internet Services

Web Development

Digital Marketing

Consumer Tech