Results 1 to 2 of 2
  1. #1
    Join Date
    Sep 2011
    Posts
    220

    Unanswered: update with recursive sql

    Hi,

    Please find the Recursive SQL and want to know how to use update statement

    DB2 v 9.1 Z/OS

    Table : INV_BASE_TAB

    INV_TYPE CHAR(5)
    IN_CNT_FL CHAR(1)
    CR_ID CHAR(4)

    Code:
    insert into INV_BASE_TAB
    (INV_TYP,IN_CNT_FL,CR_ID)
    
           WITH                                      
            T1                               
        ( A,INV_TYPE,INV_STRING )                  
           AS (                                      
           SELECT                                    
                                                     
          0                                          
          ,CAST('' AS VARCHAR(300))                
        ,'AAAAAG,BBBBBG,CCCCCM,'                            
          FROM SYSIBM.SYSDUMMY1                           
          UNION ALL                                  
          SELECT                                     
            A+1                                      
           , LEFT(INV_STRING,                      
                        LOCATE(',',INV_STRING)-1)  
              ,SUBSTR(INV_STRING,                  
                        LOCATE(',',INV_STRING)+1)  
             FROM  T1                        
              WHERE    A <  3     
            AND     LOCATE(',',INV_STRING) > 0      
                )                                     
        SELECT                       
                    SUBSTR(INV_TYPE,1,5)            
        , SUBSTR(STRIP(INV_TYPE),6,1) 
    ,'TEST'               
                 
             FROM  T1        WHERE A >  0
    after executing, got below records inserted
    Code:
    INV_TYPE    IN_CNT_FL    CR_ID
    AAAAA         G           TEST
    BBBBB         G           TEST
    CCCCC         M           TEST
    after inserted,suppose the INV_STRING has below value
    'AAAAAM,CCCCCG,HHHHHG,'

    means it has to update and insert

    Expected result set
    Code:
    INV_TYPE    IN_CNT_FL    CR_ID
    AAAAA         M           TEST
    BBBBB         G           TEST
    CCCCC         G           TEST
    HHHHH         G           TEST
    Please help me, how to use the UPDATE statement in that query
    Thanks

  2. #2
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    please take a look at the merge statement for help with this type requirement.
    Dave

Posting Permissions

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