title: sql server 2005 ----- how to update multiple rows using subquery
body: <p>I have 2 tables named as member and tree_str...</p>
  
  <p>"member" table as follows</p>
  
  <pre><code>self_id parent_id   position    
  100     ROOT        ROOT    
  101     100         LEFT    
  102     100         CENTRE  
  103     100         RIGHT   
  104     101         LEFT    
  105     101         CENTRE  
  106     101         RIGHT   
  </code></pre>
  
  <p>"tree_str" table as below....</p>
  
  <pre><code>self_id  L1_Left     L1_center   L1_Right  
  100     
  101     
  102     
  103     
  104     
  105     
  106
  </code></pre>
  
  <p>now i wanna populate second table using first one like this manner.....</p>
  
  <pre><code>self_id  L1_Left     L1_center   L1_Right  
  100    101             102         103  
  101    104             105         106  
  102     
  103     
  104     
  105     
  106 
  </code></pre>
  
  <p>................
  i use the query........</p>
  
  <pre><code>update tree_str 
      set tree_str.L1_Left = (select member.self_id from tree_str 
      INNER JOIN member 
      on member.parent_id=tree_str.self_id 
      AND member.position='LEFT')
      WHERE self_id IN (select member.parent_id from tree_str INNER JOIN member 
      on member.parent_id=tree_str.self_id and member.position='LEFT') 
  </code></pre>
  
  <p>but it gives the result.......</p>
  
  <pre><code>Msg 512, Level 16, State 1, Line 1
  Subquery returned more than 1 value. This is not permitted when the subquery 
  follows =, !=, &lt;, &lt;= , &gt;, &gt;= or when the subquery is used as an expression.
  The statement has been terminated.
  </code></pre>
  
  <p>so plz suggest me the right query to do it...........?????</p>