8 Replies Latest reply: Jul 5, 2013 4:45 AM by michael.kwok RSS

    update result only at inner join condition?

      i have two table : Table A and Table B

       

      Table A structure

      ID   Class  Dept  Indicator

      A    C1               Good

      B    C2               Bad

      C    C1a             Good

      D    C2a             Good

       

      Table B structure

      ID   Class

      A    C1

      B    C2

      C    C3

      D    C4

       

      According to my condition, (department value is update only when Table B.ID= Table A.ID and Table B.Class= Table A.Class and Indicator=Good)

       

      Expect result

      Finally table

      ID   Class  Dept  Indicator

      A    C1       MKT    Good

      B    C2       HR       Bad

      C    C1a                Good

      D    C2a                Good

       

      So i use left join to join two tables first and using if statement to update value, however, i got the result like as below

       

      actutal result

      Finally table

      ID   Class  Dept  Indicator

      A    C1       MKT    Good

      B    C2       HR       Bad

      C    C1a      MKT     Good

      D    C2a      MKT     Good

       

      Should i using inner join first to do transformation first and then concate it back to orginially table? Pls advise.