Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2013
    Posts
    2

    Unanswered: Update or merge field from 2nd table - DB2 iSeries

    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

  2. #2
    Join Date
    Jan 2012
    Posts
    20
    What query did you try and what error message did you get?

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    UPDATE tbl_target.CUSTNUM=tbl_source.CUSTNUM_NEW WHERE tbl_target.CUSTNUM = tbl_source_OLD ONLY getting the records from tbl_source.MASTER=0
    Please see manuals for basic syntax of UPDATE statement.
    UPDATE
    Code:
    Syntax
    Searched UPDATE: 
    
    >>-UPDATE--+-table-name-+--+--------------------+--------------->
               '-view-name--'  '-correlation-clause-'   
    
    >--+-------------------------+--SET--assignment-clause---------->
       +-OVERRIDING SYSTEM VALUE-+                           
       '-OVERRIDING USER VALUE---'                           
    
    >--+-------------------------+---------------------------------->
       '-WHERE--search-condition-'   
    
    ...
    ...
    You might thought that it was apparent the follwing code(a) means something like the follwing code(b), by the names of tables and columns.
    (a) UPDATE tbl_target.CUSTNUM=tbl_source.CUSTNUM_NEW ...
    (b) UPDATE tbl_target SET tbl_target.CUSTNUM = (SELECT tbl_source.CUSTNUM_NEW ...)

    But, it is not so clear for some peoples(including me..).
    Every time seeing the abbreviated code, I need some effort to understand the meaning of the abbreviated code, by fulfilling some code.

    So, please try to supply whole code without abbeviations.


    Anyway, the following examples might be worth to try.

    Example 1:
    Code:
    UPDATE tbl_target AS t
     SET   custnum
         = (SELECT custnum_new
             FROM  tbl_source AS s
             WHERE s.custnum_old = t.custnum
               AND s.master = 0
           )
     WHERE EXISTS
           (SELECT 0 /*custnum_new*/
             FROM  tbl_source AS s
             WHERE s.custnum_old = t.custnum
               AND s.master = 0
           )
    ;
    MERGE

    Example 2:
    Code:
    MERGE
     INTO  tbl_target AS t
     USING (SELECT custnum_old
                 , custnum_new
             FROM  tbl_source
             WHERE master = 0
           ) AS s
      ON   s.custnum_old = t.custnum
    WHEN MATCHED THEN
    UPDATE
     SET   custnum = s.custnum_new
    ;
    Last edited by tonkuma; 03-01-13 at 16:38. Reason: Add "UPDATE" keyword to Example 2. Add a link to MERGE statement.

  4. #4
    Join Date
    Mar 2013
    Posts
    2
    First off, thank you for the replies. I am very appreciative for the assistance.

    Here is the sql I used and got to work. The major part of my problem was that it only updated 1 record out of 30,000 which I found to be odd. In fact it was accurate.

    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;

Posting Permissions

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