Update or merge field from 2nd table - DB2 iSeries

[martyy]

I am working on an iSeries v7r1 to consolidate customer accounts after de-deuplication. I am struggling with a fairly simply update to a single field from a value in another table.

tbl_target
CUSTNUM

tbl_source
CUSTNUM_NEW
CUSTNUM_OLD
MASTER

What I need to do is
UPDATE tbl_target.CUSTNUM=tbl_source.CUSTNUM_NEW WHERE tbl_target.CUSTNUM = tbl_source_OLD ONLY getting the records from tbl_source.MASTER=0


I can post the SQL I have tried tomorrow but thought it might be easier explained without.

[martyy]

I was able to do the merge using the following code.

MERGE INTO tbltarget AS A
 
USING (
 
SELECT B.CUSTNUM_NEW, B.CUSTNUM_OLD, B.MASTER FROM tblsource B 
) AS B ON (
 
B.CUSTNUM_OLD = A.CUSTNUM AND B.MASTER='0'
 
)
 
WHEN MATCHED THEN
 
UPDATE SET 
A.CUSTNUM = B.CUSTNUM;