title: Update based on conditions in SQL Server using self join
body: <p>I have a table like below in SQL Server 2008.</p>
  
  <pre><code>ID| ParentID| InvID |IsIntTr|   IsTR
  1 |   NULL  |     1 |  NULL |   NULL
  2 |      1  |     2 |  NULL |   NULL
  3 |      1  |     1 |  NULL |   NULL
  </code></pre>
  
  <p>My requirement is: <br />
  If ParentID is populated 
  Compare the parent’s InvID to the child’s InvID.  If they are populated and they are the same value then set the IsIntTr=1 and IsTr=0.  Otherwise, set the IsIntTr=NULL and the IsTr =1 <br /><br />
  Else (i.e ParentID is null) <br /><br />
   Set IsIntTr=NULL and IsTr=NULL </p>
  
  <p>I have written the following SQL update query, however, it does not work as expected:</p>
  
  <pre><code>UPDATE child SET
  
  IsIntTr =   
     CASE WHEN child.ParentID IS NULL THEN NULL
          WHEN child.InvID = parent.InvID AND child.ParentID IS NOT NULL
                  THEN 1 ELSE NULL END,
  IsTr     =  
      CASE WHEN child.ParentID IS NULL THEN NULL  
          WHEN child.InvID = parent.InvID AND child.ParentID IS NOT NULL
                  THEN 0 ELSE 1 END
      from    MyTable child (NOLOCK),     
              MyTable parent (NOLOCK)
  </code></pre>
  
  <p>Any help is greatly appreciated!</p>
  
  <p>Thanks  </p>