title: Inner join within update statement in PostgreSQL
body: <p>I have table called temp_table which consist of following rows:</p>
  
  <pre><code> cola colb result
   ----------------
    p4   s1    0
    p8   s1    0
    p9   s1    0
    p5   f1    0
    p8   f1    0
  </code></pre>
  
  <p>Now I need to update result column with the count(*) of colb. For which i am trying the following query:</p>
  
  <pre><code>update tem_table
  set result = x.result
  from tem_table tt
  inner join(select colb,count(*) as result from tem_table group by colb) x
  on x.colb = tt.colb;
  </code></pre>
  
  <p>And selecting distinct colb and result from temp_table:</p>
  
  <pre><code>select distinct colb,result from tem_table;
  </code></pre>
  
  <p>Getting output:</p>
  
  <pre><code>colb result
  -----------
   s1    3
   f1    3
  </code></pre>
  
  <p>But the expected output is:</p>
  
  <pre><code>colb result
  -----------
   s1    3
   f1    2
  </code></pre>
  
  <p>I am not getting where I am getting wrong in my query? Please help me.Thanks</p>