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

    Unanswered: update query with table lock

    Hi;

    DB2 9.1 z/os
    The below update query will not perform faster..selecting the column values from the table and update to the same table to diferent columns
    Code:
    UPDATE SAM_TABLE T1                     
    SET (T1.COL_CAP,T1.DT_PUR)= 
    ( SELECT T2.COL_CAP_MA,T2.DT_PUR_MA    
         FROM SAM_TABLE T2                  
      WHERE                                    
      T1.LOC_PL         = T2.LOC_PL     
      AND T1.BS_NO    = T2.BS_NO   
      AND T1.BT_NO  = T2.BT_NO 
      
      )                                        
     WHERE                                     
         T1.COL_CAP=0
    LOC_PL,BS_NO,BT_NO are composite keys
    datatypes are CHAR

    While updating we are facing the table locking erros
    Please help

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    You might want to update some columns by values of other columns in same row.
    If so, remove the subquery.
    I'm curious why you want to use the subquery.

  3. #3
    Join Date
    Sep 2011
    Posts
    220
    Hi
    Thanks for the reply..i removed the sub query

    Code:
    UPDATE SAM_TABLE T1                     
    SET T1.COL_CAP=T1.COL_CAP_MA
    and T1.DT_PUR= T1.DT_PUR_MA 
    WHERE                                     
         T1.COL_CAP=0
    Please let me know,the above query will work

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    May be No!

    Try by replacing "and" with ",".

    And all qualifier "T1." must be unnecessary.(It only harmed readability for me.)

  5. #5
    Join Date
    Sep 2011
    Posts
    220
    thanks,working good

Posting Permissions

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