locked
Delete Data Base on condation RRS feed

  • Question

  • Want to delete Few columns data from a table based on condation
    There is a column name Facility Type
    if Facility Type is A then delete Col1,Col2,Col3
    if Facility Type is B then delete Col4,Col5,Col6
    if Facility Type is C then delete Col7,Col5,Col8

    Please Suggest

    Tuesday, January 29, 2013 8:45 AM

Answers

  • Solution

    Update tbl_COG_TMP_Facilities
    Set
     [Facility_TypeDetails] = (Case WHEN facility_type = 'VLDef38_7861'   THEN [Facility_TypeDetails]
             WHEN facility_type = 'VLD_FACILITYTYPE_R14' THEN [Facility_TypeDetails]

             WHEN facility_type  = 'VLDef38_10518'  THEN [Facility_TypeDetails]
             WHEN facility_type  = 'VLDef38_10519'  THEN [Facility_TypeDetails]
             WHEN facility_type  = 'VLDef38_10520'  THEN [Facility_TypeDetails]
             WHEN facility_type  = 'VLDef38_10521'  THEN [Facility_TypeDetails]
             WHEN facility_type  = 'VLDef38_10522'  THEN [Facility_TypeDetails]
             WHEN facility_type  = 'VLDef38_10515'  THEN [Facility_TypeDetails]

             WHEN facility_type ='VLD_FACILITYTYPE_R65' THEN [Facility_TypeDetails]

             WHEN facility_type = 'VLD_FACILITYTYPE_R61' THEN ''

             END),
      
     [Facility_Purpose] = (Case  WHEN facility_type = 'VLDef38_7861'   THEN [Facility_Purpose]
             WHEN facility_type = 'VLD_FACILITYTYPE_R14' THEN [Facility_Purpose]

             WHEN facility_type  = 'VLDef38_10518'  THEN [Facility_Purpose]
             WHEN facility_type  = 'VLDef38_10519'  THEN [Facility_Purpose]
             WHEN facility_type  = 'VLDef38_10520'  THEN [Facility_Purpose]
             WHEN facility_type  = 'VLDef38_10521'  THEN [Facility_Purpose]
             WHEN facility_type  = 'VLDef38_10522'  THEN [Facility_Purpose]
             WHEN facility_type  = 'VLDef38_10515'  THEN [Facility_Purpose]

             WHEN facility_type ='VLD_FACILITYTYPE_R65' THEN [Facility_Purpose]

             WHEN facility_type = 'VLD_FACILITYTYPE_R61' THEN ''

             END), 
      
     [Facility_OthCurrency1] = (Case WHEN facility_type = 'VLDef38_7861'   THEN [Facility_OthCurrency1]
             WHEN facility_type = 'VLD_FACILITYTYPE_R14' THEN [Facility_OthCurrency1]

             WHEN facility_type  = 'VLDef38_10518'  THEN [Facility_OthCurrency1]
             WHEN facility_type  = 'VLDef38_10519'  THEN [Facility_OthCurrency1]
             WHEN facility_type  = 'VLDef38_10520'  THEN [Facility_OthCurrency1]
             WHEN facility_type  = 'VLDef38_10521'  THEN [Facility_OthCurrency1]
             WHEN facility_type  = 'VLDef38_10522'  THEN [Facility_OthCurrency1]
             WHEN facility_type  = 'VLDef38_10515'  THEN [Facility_OthCurrency1]

             WHEN facility_type ='VLD_FACILITYTYPE_R65' THEN [Facility_OthCurrency1]

             WHEN facility_type = 'VLD_FACILITYTYPE_R61' THEN ''

             END),

     [Facility_OthCurrency2] = (Case WHEN facility_type = 'VLDef38_7861'   THEN [Facility_OthCurrency2]
             WHEN facility_type = 'VLD_FACILITYTYPE_R14' THEN [Facility_OthCurrency2]

             WHEN facility_type  = 'VLDef38_10518'  THEN [Facility_OthCurrency2]
             WHEN facility_type  = 'VLDef38_10519'  THEN [Facility_OthCurrency2]
             WHEN facility_type  = 'VLDef38_10520'  THEN [Facility_OthCurrency2]
             WHEN facility_type  = 'VLDef38_10521'  THEN [Facility_OthCurrency2]
             WHEN facility_type  = 'VLDef38_10522'  THEN [Facility_OthCurrency2]
             WHEN facility_type  = 'VLDef38_10515'  THEN [Facility_OthCurrency2]

             WHEN facility_type ='VLD_FACILITYTYPE_R65' THEN [Facility_OthCurrency2]

             WHEN facility_type = 'VLD_FACILITYTYPE_R61' THEN ''

             END), 

     [Facility_OthCurrency3] = (Case WHEN facility_type = 'VLDef38_7861'   THEN [Facility_OthCurrency3]
             WHEN facility_type = 'VLD_FACILITYTYPE_R14' THEN [Facility_OthCurrency3]

             WHEN facility_type  = 'VLDef38_10518'  THEN [Facility_OthCurrency3]
             WHEN facility_type  = 'VLDef38_10519'  THEN [Facility_OthCurrency3]
             WHEN facility_type  = 'VLDef38_10520'  THEN [Facility_OthCurrency3]
             WHEN facility_type  = 'VLDef38_10521'  THEN [Facility_OthCurrency3]
             WHEN facility_type  = 'VLDef38_10522'  THEN [Facility_OthCurrency3]
             WHEN facility_type  = 'VLDef38_10515'  THEN [Facility_OthCurrency3]

             WHEN facility_type ='VLD_FACILITYTYPE_R65' THEN [Facility_OthCurrency3]

             WHEN facility_type = 'VLD_FACILITYTYPE_R61' THEN ''

             END),

     [Facility_Basis]  = (Case WHEN facility_type = 'VLDef38_7861'   THEN [Facility_Basis]
             WHEN facility_type = 'VLD_FACILITYTYPE_R14' THEN [Facility_Basis]

             WHEN facility_type  = 'VLDef38_10518'  THEN [Facility_Basis]
             WHEN facility_type  = 'VLDef38_10519'  THEN [Facility_Basis]
             WHEN facility_type  = 'VLDef38_10520'  THEN [Facility_Basis]
             WHEN facility_type  = 'VLDef38_10521'  THEN [Facility_Basis]
             WHEN facility_type  = 'VLDef38_10522'  THEN [Facility_Basis]
             WHEN facility_type  = 'VLDef38_10515'  THEN [Facility_Basis]

             WHEN facility_type ='VLD_FACILITYTYPE_R65' THEN [Facility_Basis]

             WHEN facility_type = 'VLD_FACILITYTYPE_R61' THEN ''

             END), 
     
     [Facility_IntRateTerm] = (Case WHEN facility_type = 'VLDef38_7861'   THEN [Facility_IntRateTerm]
             WHEN facility_type = 'VLD_FACILITYTYPE_R14' THEN [Facility_IntRateTerm]

             WHEN facility_type  = 'VLDef38_10518'  THEN [Facility_IntRateTerm]
             WHEN facility_type  = 'VLDef38_10519'  THEN [Facility_IntRateTerm]
             WHEN facility_type  = 'VLDef38_10520'  THEN [Facility_IntRateTerm]
             WHEN facility_type  = 'VLDef38_10521'  THEN [Facility_IntRateTerm]
             WHEN facility_type  = 'VLDef38_10522'  THEN [Facility_IntRateTerm]
             WHEN facility_type  = 'VLDef38_10515'  THEN [Facility_IntRateTerm]

             WHEN facility_type ='VLD_FACILITYTYPE_R65' THEN [Facility_IntRateTerm]

             WHEN facility_type = 'VLD_FACILITYTYPE_R61' THEN ''

             END),

     [Facility_DefIntRate] = (Case WHEN facility_type = 'VLDef38_7861'   THEN [Facility_DefIntRate]
             WHEN facility_type = 'VLD_FACILITYTYPE_R14' THEN [Facility_DefIntRate]

             WHEN facility_type  = 'VLDef38_10518'  THEN [Facility_DefIntRate]
             WHEN facility_type  = 'VLDef38_10519'  THEN [Facility_DefIntRate]
             WHEN facility_type  = 'VLDef38_10520'  THEN [Facility_DefIntRate]
             WHEN facility_type  = 'VLDef38_10521'  THEN [Facility_DefIntRate]
             WHEN facility_type  = 'VLDef38_10522'  THEN [Facility_DefIntRate]
             WHEN facility_type  = 'VLDef38_10515'  THEN [Facility_DefIntRate]

             WHEN facility_type ='VLD_FACILITYTYPE_R65' THEN [Facility_DefIntRate]

             WHEN facility_type = 'VLD_FACILITYTYPE_R61' THEN ''

             END), 

     [Facility_DisMargin] = (Case WHEN facility_type = 'VLDef38_7861'   THEN [Facility_DisMargin]
             WHEN facility_type = 'VLD_FACILITYTYPE_R14' THEN [Facility_DisMargin]

             WHEN facility_type  = 'VLDef38_10518'  THEN [Facility_DisMargin]
             WHEN facility_type  = 'VLDef38_10519'  THEN [Facility_DisMargin]
             WHEN facility_type  = 'VLDef38_10520'  THEN [Facility_DisMargin]
             WHEN facility_type  = 'VLDef38_10521'  THEN [Facility_DisMargin]
             WHEN facility_type  = 'VLDef38_10522'  THEN [Facility_DisMargin]
             WHEN facility_type  = 'VLDef38_10515'  THEN [Facility_DisMargin]

             WHEN facility_type ='VLD_FACILITYTYPE_R65' THEN [Facility_DisMargin]

             WHEN facility_type = 'VLD_FACILITYTYPE_R61' THEN ''

             END),

     [Facility_DisBasis]  = (Case WHEN facility_type = 'VLDef38_7861'   THEN [Facility_DisBasis]
             WHEN facility_type = 'VLD_FACILITYTYPE_R14' THEN [Facility_DisBasis]

             WHEN facility_type  = 'VLDef38_10518'  THEN [Facility_DisBasis]
             WHEN facility_type  = 'VLDef38_10519'  THEN [Facility_DisBasis]
             WHEN facility_type  = 'VLDef38_10520'  THEN [Facility_DisBasis]
             WHEN facility_type  = 'VLDef38_10521'  THEN [Facility_DisBasis]
             WHEN facility_type  = 'VLDef38_10522'  THEN [Facility_DisBasis]
             WHEN facility_type  = 'VLDef38_10515'  THEN [Facility_DisBasis]

             WHEN facility_type ='VLD_FACILITYTYPE_R65' THEN [Facility_DisBasis]
     
             WHEN facility_type = 'VLD_FACILITYTYPE_R61' THEN ''

             END),

     [Facility_DisUser]  = (Case WHEN facility_type = 'VLDef38_7861'   THEN [Facility_DisUser]
             WHEN facility_type = 'VLD_FACILITYTYPE_R14' THEN [Facility_DisUser]

             WHEN facility_type  = 'VLDef38_10518'  THEN [Facility_DisUser]
             WHEN facility_type  = 'VLDef38_10519'  THEN [Facility_DisUser]
             WHEN facility_type  = 'VLDef38_10520'  THEN [Facility_DisUser]
             WHEN facility_type  = 'VLDef38_10521'  THEN [Facility_DisUser]
             WHEN facility_type  = 'VLDef38_10522'  THEN [Facility_DisUser]
             WHEN facility_type  = 'VLDef38_10515'  THEN [Facility_DisUser]

             WHEN facility_type ='VLD_FACILITYTYPE_R65' THEN [Facility_DisUser]

             WHEN facility_type = 'VLD_FACILITYTYPE_R61' THEN ''

             END),

     [Facility_DisDate]  = (Case WHEN facility_type = 'VLDef38_7861'   THEN [Facility_DisDate]
             WHEN facility_type = 'VLD_FACILITYTYPE_R14' THEN [Facility_DisDate]

             WHEN facility_type  = 'VLDef38_10518'  THEN [Facility_DisDate]
             WHEN facility_type  = 'VLDef38_10519'  THEN [Facility_DisDate]
             WHEN facility_type  = 'VLDef38_10520'  THEN [Facility_DisDate]
             WHEN facility_type  = 'VLDef38_10521'  THEN [Facility_DisDate]
             WHEN facility_type  = 'VLDef38_10522'  THEN [Facility_DisDate]
             WHEN facility_type  = 'VLDef38_10515'  THEN [Facility_DisDate]

             WHEN facility_type ='VLD_FACILITYTYPE_R65' THEN [Facility_DisDate]

             WHEN facility_type = 'VLD_FACILITYTYPE_R61' THEN ''

             END),

     [Facility_AvlPeriod] = (Case WHEN facility_type = 'VLDef38_7861'   THEN [Facility_AvlPeriod]
             WHEN facility_type = 'VLD_FACILITYTYPE_R14' THEN [Facility_AvlPeriod]

             WHEN facility_type  = 'VLDef38_10518'  THEN [Facility_AvlPeriod]
             WHEN facility_type  = 'VLDef38_10519'  THEN [Facility_AvlPeriod]
             WHEN facility_type  = 'VLDef38_10520'  THEN [Facility_AvlPeriod]
             WHEN facility_type  = 'VLDef38_10521'  THEN [Facility_AvlPeriod]
             WHEN facility_type  = 'VLDef38_10522'  THEN [Facility_AvlPeriod]
             WHEN facility_type  = 'VLDef38_10515'  THEN [Facility_AvlPeriod]

             WHEN facility_type ='VLD_FACILITYTYPE_R65' THEN [Facility_AvlPeriod]

             WHEN facility_type = 'VLD_FACILITYTYPE_R61' THEN ''

             END),

     [Facility_MaxIntPeriod] = (Case WHEN facility_type = 'VLDef38_7861'   THEN ''
             WHEN facility_type = 'VLD_FACILITYTYPE_R14' THEN ''

             WHEN facility_type  = 'VLDef38_10518'  THEN ''
             WHEN facility_type  = 'VLDef38_10519'  THEN ''
             WHEN facility_type  = 'VLDef38_10520'  THEN ''
             WHEN facility_type  = 'VLDef38_10521'  THEN ''
             WHEN facility_type  = 'VLDef38_10522'  THEN ''
             WHEN facility_type  = 'VLDef38_10515'  THEN ''

             WHEN facility_type ='VLD_FACILITYTYPE_R65' THEN ''

             WHEN facility_type = 'VLD_FACILITYTYPE_R61' THEN [Facility_MaxIntPeriod]

             END),

     [Facility_MaxTenor]  = (Case WHEN facility_type = 'VLDef38_7861'   THEN ''
             WHEN facility_type = 'VLD_FACILITYTYPE_R14' THEN ''

             WHEN facility_type  = 'VLDef38_10518'  THEN [Facility_MaxTenor]
             WHEN facility_type  = 'VLDef38_10519'  THEN [Facility_MaxTenor]
             WHEN facility_type  = 'VLDef38_10520'  THEN [Facility_MaxTenor]
             WHEN facility_type  = 'VLDef38_10521'  THEN [Facility_MaxTenor]
             WHEN facility_type  = 'VLDef38_10522'  THEN [Facility_MaxTenor]
             WHEN facility_type  = 'VLDef38_10515'  THEN [Facility_MaxTenor]

             WHEN facility_type ='VLD_FACILITYTYPE_R65' THEN ''

             WHEN facility_type = 'VLD_FACILITYTYPE_R61' THEN [Facility_MaxTenor]

             END), 

     [Facility_MargBuildupRate]=(Case WHEN facility_type = 'VLDef38_7861'   THEN ''
              WHEN facility_type = 'VLD_FACILITYTYPE_R14' THEN ''

              WHEN facility_type  = 'VLDef38_10518'  THEN ''
              WHEN facility_type  = 'VLDef38_10519'  THEN ''
              WHEN facility_type  = 'VLDef38_10520'  THEN ''
              WHEN facility_type  = 'VLDef38_10521'  THEN ''
              WHEN facility_type  = 'VLDef38_10522'  THEN ''
              WHEN facility_type  = 'VLDef38_10515'  THEN ''

              WHEN facility_type ='VLD_FACILITYTYPE_R65' THEN ''

              WHEN facility_type = 'VLD_FACILITYTYPE_R61' THEN [Facility_MargBuildupRate]

             END),

     [Facility_MargBuildupFreq] = (Case WHEN facility_type = 'VLDef38_7861'   THEN ''
              WHEN facility_type = 'VLD_FACILITYTYPE_R14' THEN ''

              WHEN facility_type  = 'VLDef38_10518'  THEN ''
              WHEN facility_type  = 'VLDef38_10519'  THEN ''
              WHEN facility_type  = 'VLDef38_10520'  THEN ''
              WHEN facility_type  = 'VLDef38_10521'  THEN ''
              WHEN facility_type  = 'VLDef38_10522'  THEN ''
              WHEN facility_type  = 'VLDef38_10515'  THEN ''

              WHEN facility_type ='VLD_FACILITYTYPE_R65' THEN ''

              WHEN facility_type = 'VLD_FACILITYTYPE_R61' THEN [Facility_MargBuildupFreq]

             END),
     [Facility_MarginBuildupAmt] = (Case WHEN facility_type = 'VLDef38_7861'   THEN ''
              WHEN facility_type = 'VLD_FACILITYTYPE_R14' THEN ''

              WHEN facility_type  = 'VLDef38_10518'  THEN ''
              WHEN facility_type  = 'VLDef38_10519'  THEN ''
              WHEN facility_type  = 'VLDef38_10520'  THEN ''
              WHEN facility_type  = 'VLDef38_10521'  THEN ''
              WHEN facility_type  = 'VLDef38_10522'  THEN ''
              WHEN facility_type  = 'VLDef38_10515'  THEN ''

              WHEN facility_type ='VLD_FACILITYTYPE_R65' THEN ''

              WHEN facility_type = 'VLD_FACILITYTYPE_R61' THEN [Facility_MarginBuildupAmt]

             END),

     [Facility_MarginBuildupCur] = (Case WHEN facility_type = 'VLDef38_7861'   THEN ''
              WHEN facility_type = 'VLD_FACILITYTYPE_R14' THEN ''

              WHEN facility_type  = 'VLDef38_10518'  THEN ''
              WHEN facility_type  = 'VLDef38_10519'  THEN ''
              WHEN facility_type  = 'VLDef38_10520'  THEN ''
              WHEN facility_type  = 'VLDef38_10521'  THEN ''
              WHEN facility_type  = 'VLDef38_10522'  THEN ''
              WHEN facility_type  = 'VLDef38_10515'  THEN ''

              WHEN facility_type ='VLD_FACILITYTYPE_R65' THEN ''

              WHEN facility_type = 'VLD_FACILITYTYPE_R61' THEN [Facility_MarginBuildupCur]

             END),
     [Facility_CoverType] = (Case WHEN facility_type = 'VLDef38_7861'   THEN ''
             WHEN facility_type = 'VLD_FACILITYTYPE_R14' THEN ''

             WHEN facility_type  = 'VLDef38_10518'  THEN [Facility_CoverType]
             WHEN facility_type  = 'VLDef38_10519'  THEN [Facility_CoverType]
             WHEN facility_type  = 'VLDef38_10520'  THEN [Facility_CoverType]
             WHEN facility_type  = 'VLDef38_10521'  THEN [Facility_CoverType]
             WHEN facility_type  = 'VLDef38_10522'  THEN [Facility_CoverType]
             WHEN facility_type  = 'VLDef38_10515'  THEN [Facility_CoverType]

             WHEN facility_type ='VLD_FACILITYTYPE_R65' THEN ''

             WHEN facility_type = 'VLD_FACILITYTYPE_R61' THEN ''

             END),  
     [Facility_MinCoverAmount] = (Case WHEN facility_type = 'VLDef38_7861'   THEN ''
              WHEN facility_type = 'VLD_FACILITYTYPE_R14' THEN ''

              WHEN facility_type  = 'VLDef38_10518'  THEN [Facility_MinCoverAmount]
              WHEN facility_type  = 'VLDef38_10519'  THEN [Facility_MinCoverAmount]
              WHEN facility_type  = 'VLDef38_10520'  THEN [Facility_MinCoverAmount]
              WHEN facility_type  = 'VLDef38_10521'  THEN [Facility_MinCoverAmount]
              WHEN facility_type  = 'VLDef38_10522'  THEN [Facility_MinCoverAmount]
              WHEN facility_type  = 'VLDef38_10515'  THEN [Facility_MinCoverAmount]

              WHEN facility_type ='VLD_FACILITYTYPE_R65' THEN ''

              WHEN facility_type = 'VLD_FACILITYTYPE_R61' THEN ''

             END),

     [Facility_Beneficiary] = (Case WHEN facility_type = 'VLDef38_7861'   THEN ''
             WHEN facility_type = 'VLD_FACILITYTYPE_R14' THEN ''

             WHEN facility_type  = 'VLDef38_10518'  THEN ''
             WHEN facility_type  = 'VLDef38_10519'  THEN ''
             WHEN facility_type  = 'VLDef38_10520'  THEN ''
             WHEN facility_type  = 'VLDef38_10521'  THEN ''
             WHEN facility_type  = 'VLDef38_10522'  THEN ''
             WHEN facility_type  = 'VLDef38_10515'  THEN ''

             WHEN facility_type ='VLD_FACILITYTYPE_R65' THEN ''

             WHEN facility_type = 'VLD_FACILITYTYPE_R61' THEN ''

             END)

    • Marked as answer by SR_MCTS Friday, February 1, 2013 8:21 AM
    Friday, February 1, 2013 8:19 AM

All replies

  • I think we can delete a row not a column..you can update its value....

    UPDATE yrtable 

    SET Col1 = CASE WHEN FacilityType = 'A' THEN '' ELSE COl1 END

    .....

    Or 3 differents updates

    UPDATE yetable SET Col1='',col2='',col3='' WHERE FacilityType= 'A'


    Thanks and regards, Rishabh K

    Tuesday, January 29, 2013 8:53 AM
  • Try

    update table1 set col1=null,col2=null,col3=null
    where [Facility Type]='A';
    update table1 set col4=null,col5=null,col6=null
    where [Facility Type]='B';
    update table1 set col7=null,col5=null,col8=null
    where [Facility Type]='C';


    Many Thanks & Best Regards, Hua Min

    Tuesday, January 29, 2013 9:01 AM
  • Why do you want to delete particular column instead of the row? can you post the ddl and an example of how the data is ..

    Please mark as helpful and propose as answer if you find this as correct!!! Thanks, Rakesh.

    Tuesday, January 29, 2013 9:03 AM
  • I think you just want to make the columns empty 

    update table1 set
     col1 = case when [Facility Type]='A'
    	then null else Col1 end	
    Col2=.....

    you wont be able to track here actually how many rows were updated.

    Rather using three separate update statements will be good


    Regards
    Satheesh

    Tuesday, January 29, 2013 9:24 AM
  • Solution

    Update tbl_COG_TMP_Facilities
    Set
     [Facility_TypeDetails] = (Case WHEN facility_type = 'VLDef38_7861'   THEN [Facility_TypeDetails]
             WHEN facility_type = 'VLD_FACILITYTYPE_R14' THEN [Facility_TypeDetails]

             WHEN facility_type  = 'VLDef38_10518'  THEN [Facility_TypeDetails]
             WHEN facility_type  = 'VLDef38_10519'  THEN [Facility_TypeDetails]
             WHEN facility_type  = 'VLDef38_10520'  THEN [Facility_TypeDetails]
             WHEN facility_type  = 'VLDef38_10521'  THEN [Facility_TypeDetails]
             WHEN facility_type  = 'VLDef38_10522'  THEN [Facility_TypeDetails]
             WHEN facility_type  = 'VLDef38_10515'  THEN [Facility_TypeDetails]

             WHEN facility_type ='VLD_FACILITYTYPE_R65' THEN [Facility_TypeDetails]

             WHEN facility_type = 'VLD_FACILITYTYPE_R61' THEN ''

             END),
      
     [Facility_Purpose] = (Case  WHEN facility_type = 'VLDef38_7861'   THEN [Facility_Purpose]
             WHEN facility_type = 'VLD_FACILITYTYPE_R14' THEN [Facility_Purpose]

             WHEN facility_type  = 'VLDef38_10518'  THEN [Facility_Purpose]
             WHEN facility_type  = 'VLDef38_10519'  THEN [Facility_Purpose]
             WHEN facility_type  = 'VLDef38_10520'  THEN [Facility_Purpose]
             WHEN facility_type  = 'VLDef38_10521'  THEN [Facility_Purpose]
             WHEN facility_type  = 'VLDef38_10522'  THEN [Facility_Purpose]
             WHEN facility_type  = 'VLDef38_10515'  THEN [Facility_Purpose]

             WHEN facility_type ='VLD_FACILITYTYPE_R65' THEN [Facility_Purpose]

             WHEN facility_type = 'VLD_FACILITYTYPE_R61' THEN ''

             END), 
      
     [Facility_OthCurrency1] = (Case WHEN facility_type = 'VLDef38_7861'   THEN [Facility_OthCurrency1]
             WHEN facility_type = 'VLD_FACILITYTYPE_R14' THEN [Facility_OthCurrency1]

             WHEN facility_type  = 'VLDef38_10518'  THEN [Facility_OthCurrency1]
             WHEN facility_type  = 'VLDef38_10519'  THEN [Facility_OthCurrency1]
             WHEN facility_type  = 'VLDef38_10520'  THEN [Facility_OthCurrency1]
             WHEN facility_type  = 'VLDef38_10521'  THEN [Facility_OthCurrency1]
             WHEN facility_type  = 'VLDef38_10522'  THEN [Facility_OthCurrency1]
             WHEN facility_type  = 'VLDef38_10515'  THEN [Facility_OthCurrency1]

             WHEN facility_type ='VLD_FACILITYTYPE_R65' THEN [Facility_OthCurrency1]

             WHEN facility_type = 'VLD_FACILITYTYPE_R61' THEN ''

             END),

     [Facility_OthCurrency2] = (Case WHEN facility_type = 'VLDef38_7861'   THEN [Facility_OthCurrency2]
             WHEN facility_type = 'VLD_FACILITYTYPE_R14' THEN [Facility_OthCurrency2]

             WHEN facility_type  = 'VLDef38_10518'  THEN [Facility_OthCurrency2]
             WHEN facility_type  = 'VLDef38_10519'  THEN [Facility_OthCurrency2]
             WHEN facility_type  = 'VLDef38_10520'  THEN [Facility_OthCurrency2]
             WHEN facility_type  = 'VLDef38_10521'  THEN [Facility_OthCurrency2]
             WHEN facility_type  = 'VLDef38_10522'  THEN [Facility_OthCurrency2]
             WHEN facility_type  = 'VLDef38_10515'  THEN [Facility_OthCurrency2]

             WHEN facility_type ='VLD_FACILITYTYPE_R65' THEN [Facility_OthCurrency2]

             WHEN facility_type = 'VLD_FACILITYTYPE_R61' THEN ''

             END), 

     [Facility_OthCurrency3] = (Case WHEN facility_type = 'VLDef38_7861'   THEN [Facility_OthCurrency3]
             WHEN facility_type = 'VLD_FACILITYTYPE_R14' THEN [Facility_OthCurrency3]

             WHEN facility_type  = 'VLDef38_10518'  THEN [Facility_OthCurrency3]
             WHEN facility_type  = 'VLDef38_10519'  THEN [Facility_OthCurrency3]
             WHEN facility_type  = 'VLDef38_10520'  THEN [Facility_OthCurrency3]
             WHEN facility_type  = 'VLDef38_10521'  THEN [Facility_OthCurrency3]
             WHEN facility_type  = 'VLDef38_10522'  THEN [Facility_OthCurrency3]
             WHEN facility_type  = 'VLDef38_10515'  THEN [Facility_OthCurrency3]

             WHEN facility_type ='VLD_FACILITYTYPE_R65' THEN [Facility_OthCurrency3]

             WHEN facility_type = 'VLD_FACILITYTYPE_R61' THEN ''

             END),

     [Facility_Basis]  = (Case WHEN facility_type = 'VLDef38_7861'   THEN [Facility_Basis]
             WHEN facility_type = 'VLD_FACILITYTYPE_R14' THEN [Facility_Basis]

             WHEN facility_type  = 'VLDef38_10518'  THEN [Facility_Basis]
             WHEN facility_type  = 'VLDef38_10519'  THEN [Facility_Basis]
             WHEN facility_type  = 'VLDef38_10520'  THEN [Facility_Basis]
             WHEN facility_type  = 'VLDef38_10521'  THEN [Facility_Basis]
             WHEN facility_type  = 'VLDef38_10522'  THEN [Facility_Basis]
             WHEN facility_type  = 'VLDef38_10515'  THEN [Facility_Basis]

             WHEN facility_type ='VLD_FACILITYTYPE_R65' THEN [Facility_Basis]

             WHEN facility_type = 'VLD_FACILITYTYPE_R61' THEN ''

             END), 
     
     [Facility_IntRateTerm] = (Case WHEN facility_type = 'VLDef38_7861'   THEN [Facility_IntRateTerm]
             WHEN facility_type = 'VLD_FACILITYTYPE_R14' THEN [Facility_IntRateTerm]

             WHEN facility_type  = 'VLDef38_10518'  THEN [Facility_IntRateTerm]
             WHEN facility_type  = 'VLDef38_10519'  THEN [Facility_IntRateTerm]
             WHEN facility_type  = 'VLDef38_10520'  THEN [Facility_IntRateTerm]
             WHEN facility_type  = 'VLDef38_10521'  THEN [Facility_IntRateTerm]
             WHEN facility_type  = 'VLDef38_10522'  THEN [Facility_IntRateTerm]
             WHEN facility_type  = 'VLDef38_10515'  THEN [Facility_IntRateTerm]

             WHEN facility_type ='VLD_FACILITYTYPE_R65' THEN [Facility_IntRateTerm]

             WHEN facility_type = 'VLD_FACILITYTYPE_R61' THEN ''

             END),

     [Facility_DefIntRate] = (Case WHEN facility_type = 'VLDef38_7861'   THEN [Facility_DefIntRate]
             WHEN facility_type = 'VLD_FACILITYTYPE_R14' THEN [Facility_DefIntRate]

             WHEN facility_type  = 'VLDef38_10518'  THEN [Facility_DefIntRate]
             WHEN facility_type  = 'VLDef38_10519'  THEN [Facility_DefIntRate]
             WHEN facility_type  = 'VLDef38_10520'  THEN [Facility_DefIntRate]
             WHEN facility_type  = 'VLDef38_10521'  THEN [Facility_DefIntRate]
             WHEN facility_type  = 'VLDef38_10522'  THEN [Facility_DefIntRate]
             WHEN facility_type  = 'VLDef38_10515'  THEN [Facility_DefIntRate]

             WHEN facility_type ='VLD_FACILITYTYPE_R65' THEN [Facility_DefIntRate]

             WHEN facility_type = 'VLD_FACILITYTYPE_R61' THEN ''

             END), 

     [Facility_DisMargin] = (Case WHEN facility_type = 'VLDef38_7861'   THEN [Facility_DisMargin]
             WHEN facility_type = 'VLD_FACILITYTYPE_R14' THEN [Facility_DisMargin]

             WHEN facility_type  = 'VLDef38_10518'  THEN [Facility_DisMargin]
             WHEN facility_type  = 'VLDef38_10519'  THEN [Facility_DisMargin]
             WHEN facility_type  = 'VLDef38_10520'  THEN [Facility_DisMargin]
             WHEN facility_type  = 'VLDef38_10521'  THEN [Facility_DisMargin]
             WHEN facility_type  = 'VLDef38_10522'  THEN [Facility_DisMargin]
             WHEN facility_type  = 'VLDef38_10515'  THEN [Facility_DisMargin]

             WHEN facility_type ='VLD_FACILITYTYPE_R65' THEN [Facility_DisMargin]

             WHEN facility_type = 'VLD_FACILITYTYPE_R61' THEN ''

             END),

     [Facility_DisBasis]  = (Case WHEN facility_type = 'VLDef38_7861'   THEN [Facility_DisBasis]
             WHEN facility_type = 'VLD_FACILITYTYPE_R14' THEN [Facility_DisBasis]

             WHEN facility_type  = 'VLDef38_10518'  THEN [Facility_DisBasis]
             WHEN facility_type  = 'VLDef38_10519'  THEN [Facility_DisBasis]
             WHEN facility_type  = 'VLDef38_10520'  THEN [Facility_DisBasis]
             WHEN facility_type  = 'VLDef38_10521'  THEN [Facility_DisBasis]
             WHEN facility_type  = 'VLDef38_10522'  THEN [Facility_DisBasis]
             WHEN facility_type  = 'VLDef38_10515'  THEN [Facility_DisBasis]

             WHEN facility_type ='VLD_FACILITYTYPE_R65' THEN [Facility_DisBasis]
     
             WHEN facility_type = 'VLD_FACILITYTYPE_R61' THEN ''

             END),

     [Facility_DisUser]  = (Case WHEN facility_type = 'VLDef38_7861'   THEN [Facility_DisUser]
             WHEN facility_type = 'VLD_FACILITYTYPE_R14' THEN [Facility_DisUser]

             WHEN facility_type  = 'VLDef38_10518'  THEN [Facility_DisUser]
             WHEN facility_type  = 'VLDef38_10519'  THEN [Facility_DisUser]
             WHEN facility_type  = 'VLDef38_10520'  THEN [Facility_DisUser]
             WHEN facility_type  = 'VLDef38_10521'  THEN [Facility_DisUser]
             WHEN facility_type  = 'VLDef38_10522'  THEN [Facility_DisUser]
             WHEN facility_type  = 'VLDef38_10515'  THEN [Facility_DisUser]

             WHEN facility_type ='VLD_FACILITYTYPE_R65' THEN [Facility_DisUser]

             WHEN facility_type = 'VLD_FACILITYTYPE_R61' THEN ''

             END),

     [Facility_DisDate]  = (Case WHEN facility_type = 'VLDef38_7861'   THEN [Facility_DisDate]
             WHEN facility_type = 'VLD_FACILITYTYPE_R14' THEN [Facility_DisDate]

             WHEN facility_type  = 'VLDef38_10518'  THEN [Facility_DisDate]
             WHEN facility_type  = 'VLDef38_10519'  THEN [Facility_DisDate]
             WHEN facility_type  = 'VLDef38_10520'  THEN [Facility_DisDate]
             WHEN facility_type  = 'VLDef38_10521'  THEN [Facility_DisDate]
             WHEN facility_type  = 'VLDef38_10522'  THEN [Facility_DisDate]
             WHEN facility_type  = 'VLDef38_10515'  THEN [Facility_DisDate]

             WHEN facility_type ='VLD_FACILITYTYPE_R65' THEN [Facility_DisDate]

             WHEN facility_type = 'VLD_FACILITYTYPE_R61' THEN ''

             END),

     [Facility_AvlPeriod] = (Case WHEN facility_type = 'VLDef38_7861'   THEN [Facility_AvlPeriod]
             WHEN facility_type = 'VLD_FACILITYTYPE_R14' THEN [Facility_AvlPeriod]

             WHEN facility_type  = 'VLDef38_10518'  THEN [Facility_AvlPeriod]
             WHEN facility_type  = 'VLDef38_10519'  THEN [Facility_AvlPeriod]
             WHEN facility_type  = 'VLDef38_10520'  THEN [Facility_AvlPeriod]
             WHEN facility_type  = 'VLDef38_10521'  THEN [Facility_AvlPeriod]
             WHEN facility_type  = 'VLDef38_10522'  THEN [Facility_AvlPeriod]
             WHEN facility_type  = 'VLDef38_10515'  THEN [Facility_AvlPeriod]

             WHEN facility_type ='VLD_FACILITYTYPE_R65' THEN [Facility_AvlPeriod]

             WHEN facility_type = 'VLD_FACILITYTYPE_R61' THEN ''

             END),

     [Facility_MaxIntPeriod] = (Case WHEN facility_type = 'VLDef38_7861'   THEN ''
             WHEN facility_type = 'VLD_FACILITYTYPE_R14' THEN ''

             WHEN facility_type  = 'VLDef38_10518'  THEN ''
             WHEN facility_type  = 'VLDef38_10519'  THEN ''
             WHEN facility_type  = 'VLDef38_10520'  THEN ''
             WHEN facility_type  = 'VLDef38_10521'  THEN ''
             WHEN facility_type  = 'VLDef38_10522'  THEN ''
             WHEN facility_type  = 'VLDef38_10515'  THEN ''

             WHEN facility_type ='VLD_FACILITYTYPE_R65' THEN ''

             WHEN facility_type = 'VLD_FACILITYTYPE_R61' THEN [Facility_MaxIntPeriod]

             END),

     [Facility_MaxTenor]  = (Case WHEN facility_type = 'VLDef38_7861'   THEN ''
             WHEN facility_type = 'VLD_FACILITYTYPE_R14' THEN ''

             WHEN facility_type  = 'VLDef38_10518'  THEN [Facility_MaxTenor]
             WHEN facility_type  = 'VLDef38_10519'  THEN [Facility_MaxTenor]
             WHEN facility_type  = 'VLDef38_10520'  THEN [Facility_MaxTenor]
             WHEN facility_type  = 'VLDef38_10521'  THEN [Facility_MaxTenor]
             WHEN facility_type  = 'VLDef38_10522'  THEN [Facility_MaxTenor]
             WHEN facility_type  = 'VLDef38_10515'  THEN [Facility_MaxTenor]

             WHEN facility_type ='VLD_FACILITYTYPE_R65' THEN ''

             WHEN facility_type = 'VLD_FACILITYTYPE_R61' THEN [Facility_MaxTenor]

             END), 

     [Facility_MargBuildupRate]=(Case WHEN facility_type = 'VLDef38_7861'   THEN ''
              WHEN facility_type = 'VLD_FACILITYTYPE_R14' THEN ''

              WHEN facility_type  = 'VLDef38_10518'  THEN ''
              WHEN facility_type  = 'VLDef38_10519'  THEN ''
              WHEN facility_type  = 'VLDef38_10520'  THEN ''
              WHEN facility_type  = 'VLDef38_10521'  THEN ''
              WHEN facility_type  = 'VLDef38_10522'  THEN ''
              WHEN facility_type  = 'VLDef38_10515'  THEN ''

              WHEN facility_type ='VLD_FACILITYTYPE_R65' THEN ''

              WHEN facility_type = 'VLD_FACILITYTYPE_R61' THEN [Facility_MargBuildupRate]

             END),

     [Facility_MargBuildupFreq] = (Case WHEN facility_type = 'VLDef38_7861'   THEN ''
              WHEN facility_type = 'VLD_FACILITYTYPE_R14' THEN ''

              WHEN facility_type  = 'VLDef38_10518'  THEN ''
              WHEN facility_type  = 'VLDef38_10519'  THEN ''
              WHEN facility_type  = 'VLDef38_10520'  THEN ''
              WHEN facility_type  = 'VLDef38_10521'  THEN ''
              WHEN facility_type  = 'VLDef38_10522'  THEN ''
              WHEN facility_type  = 'VLDef38_10515'  THEN ''

              WHEN facility_type ='VLD_FACILITYTYPE_R65' THEN ''

              WHEN facility_type = 'VLD_FACILITYTYPE_R61' THEN [Facility_MargBuildupFreq]

             END),
     [Facility_MarginBuildupAmt] = (Case WHEN facility_type = 'VLDef38_7861'   THEN ''
              WHEN facility_type = 'VLD_FACILITYTYPE_R14' THEN ''

              WHEN facility_type  = 'VLDef38_10518'  THEN ''
              WHEN facility_type  = 'VLDef38_10519'  THEN ''
              WHEN facility_type  = 'VLDef38_10520'  THEN ''
              WHEN facility_type  = 'VLDef38_10521'  THEN ''
              WHEN facility_type  = 'VLDef38_10522'  THEN ''
              WHEN facility_type  = 'VLDef38_10515'  THEN ''

              WHEN facility_type ='VLD_FACILITYTYPE_R65' THEN ''

              WHEN facility_type = 'VLD_FACILITYTYPE_R61' THEN [Facility_MarginBuildupAmt]

             END),

     [Facility_MarginBuildupCur] = (Case WHEN facility_type = 'VLDef38_7861'   THEN ''
              WHEN facility_type = 'VLD_FACILITYTYPE_R14' THEN ''

              WHEN facility_type  = 'VLDef38_10518'  THEN ''
              WHEN facility_type  = 'VLDef38_10519'  THEN ''
              WHEN facility_type  = 'VLDef38_10520'  THEN ''
              WHEN facility_type  = 'VLDef38_10521'  THEN ''
              WHEN facility_type  = 'VLDef38_10522'  THEN ''
              WHEN facility_type  = 'VLDef38_10515'  THEN ''

              WHEN facility_type ='VLD_FACILITYTYPE_R65' THEN ''

              WHEN facility_type = 'VLD_FACILITYTYPE_R61' THEN [Facility_MarginBuildupCur]

             END),
     [Facility_CoverType] = (Case WHEN facility_type = 'VLDef38_7861'   THEN ''
             WHEN facility_type = 'VLD_FACILITYTYPE_R14' THEN ''

             WHEN facility_type  = 'VLDef38_10518'  THEN [Facility_CoverType]
             WHEN facility_type  = 'VLDef38_10519'  THEN [Facility_CoverType]
             WHEN facility_type  = 'VLDef38_10520'  THEN [Facility_CoverType]
             WHEN facility_type  = 'VLDef38_10521'  THEN [Facility_CoverType]
             WHEN facility_type  = 'VLDef38_10522'  THEN [Facility_CoverType]
             WHEN facility_type  = 'VLDef38_10515'  THEN [Facility_CoverType]

             WHEN facility_type ='VLD_FACILITYTYPE_R65' THEN ''

             WHEN facility_type = 'VLD_FACILITYTYPE_R61' THEN ''

             END),  
     [Facility_MinCoverAmount] = (Case WHEN facility_type = 'VLDef38_7861'   THEN ''
              WHEN facility_type = 'VLD_FACILITYTYPE_R14' THEN ''

              WHEN facility_type  = 'VLDef38_10518'  THEN [Facility_MinCoverAmount]
              WHEN facility_type  = 'VLDef38_10519'  THEN [Facility_MinCoverAmount]
              WHEN facility_type  = 'VLDef38_10520'  THEN [Facility_MinCoverAmount]
              WHEN facility_type  = 'VLDef38_10521'  THEN [Facility_MinCoverAmount]
              WHEN facility_type  = 'VLDef38_10522'  THEN [Facility_MinCoverAmount]
              WHEN facility_type  = 'VLDef38_10515'  THEN [Facility_MinCoverAmount]

              WHEN facility_type ='VLD_FACILITYTYPE_R65' THEN ''

              WHEN facility_type = 'VLD_FACILITYTYPE_R61' THEN ''

             END),

     [Facility_Beneficiary] = (Case WHEN facility_type = 'VLDef38_7861'   THEN ''
             WHEN facility_type = 'VLD_FACILITYTYPE_R14' THEN ''

             WHEN facility_type  = 'VLDef38_10518'  THEN ''
             WHEN facility_type  = 'VLDef38_10519'  THEN ''
             WHEN facility_type  = 'VLDef38_10520'  THEN ''
             WHEN facility_type  = 'VLDef38_10521'  THEN ''
             WHEN facility_type  = 'VLDef38_10522'  THEN ''
             WHEN facility_type  = 'VLDef38_10515'  THEN ''

             WHEN facility_type ='VLD_FACILITYTYPE_R65' THEN ''

             WHEN facility_type = 'VLD_FACILITYTYPE_R61' THEN ''

             END)

    • Marked as answer by SR_MCTS Friday, February 1, 2013 8:21 AM
    Friday, February 1, 2013 8:19 AM
  • Have you tried a WHERE clause for your UPDATE?

    UPDATE tbl_COG_TMP_Facilities
    SET    [Facility_TypeDetails] = ''
           ,[Facility_Purpose] = ''
           ,[Facility_OthCurrency1] = ''
           ,[Facility_OthCurrency2] = ''
           ,[Facility_OthCurrency3] = ''
           ,[Facility_Basis] = ''
           ,[Facility_IntRateTerm] = ''
           ,[Facility_DefIntRate] = ''
           ,[Facility_DisMargin] = ''
           ,[Facility_DisBasis] = ''
           ,[Facility_DisUser] = ''
           ,[Facility_DisDate] = ''
           ,[Facility_AvlPeriod] = ''
    WHERE  facility_type = 'VLD_FACILITYTYPE_R61' 
    
    
      
    UPDATE tbl_COG_TMP_Facilities
    SET    [Facility_MaxIntPeriod] = ''
           ,[Facility_MargBuildupRate] = ''
           ,[Facility_MargBuildupFreq] = ''
           ,[Facility_MarginBuildupAmt] = ''
           ,[Facility_MarginBuildupCur] = ''
    WHERE  facility_type IN ( 'VLDef38_7861', 'VLD_FACILITYTYPE_R14', 'VLDef38_10518', 'VLDef38_10519',
                              'VLDef38_10520', 'VLDef38_10521', 'VLDef38_10522', 'VLDef38_10515', 'VLD_FACILITYTYPE_R65' ) 
    
    
    UPDATE tbl_COG_TMP_Facilities
    SET    [Facility_MaxTenor] = ''
    WHERE  facility_type = 'VLDef38_7861'  OR facility_type = 'VLD_FACILITYTYPE_R14'  OR facility_type = 'VLD_FACILITYTYPE_R65' 
    
    
    UPDATE tbl_COG_TMP_Facilities
    SET    [Facility_CoverType] = ''
           ,[Facility_MinCoverAmount] = ''
    WHERE  facility_type = 'VLDef38_7861'  OR facility_type = 'VLD_FACILITYTYPE_R14'  OR facility_type = 'VLD_FACILITYTYPE_R65'  OR facility_type = 'VLD_FACILITYTYPE_R61' 
    
       
    UPDATE tbl_COG_TMP_Facilities
    SET    [Facility_Beneficiary] = ''
    WHERE  facility_type IN ( 'VLDef38_7861', 'VLD_FACILITYTYPE_R14', 'VLDef38_10518', 'VLDef38_10519',
                              'VLDef38_10520', 'VLDef38_10521', 'VLDef38_10522', 'VLDef38_10515',
                              'VLD_FACILITYTYPE_R65', 'VLD_FACILITYTYPE_R61' ) 
    

    Friday, February 1, 2013 8:45 PM