title: Update a column using Subquery in oracle
body: <p>I have an EMPLOYEE table below:</p>
  
  <pre><code>EMP_ID     DEPT_ID
  101        1
  102        2
  103        3
  104        1
  </code></pre>
  
  <p>And a DEPARTMENT table as:</p>
  
  <pre><code>DEPT_ID  COUNTS
  1   
  2   
  3   
  </code></pre>
  
  <p>I want to write a query which would count the number of Employee that belong to a department and store it into Department column table so the Department table will look like:</p>
  
  <pre><code>DEPT_ID  COUNTS
  1         2
  2         1
  3         1
  </code></pre>
  
  <p>The solution is </p>
  
  <pre><code>update department p
      set counts = (select count(*) from EMPLOYEE e where p.dept_id = e.dept_id);
  </code></pre>
  
  <p>But i really dont understand how it works internally  How does it know which dept ids in DEPARTMENT it has to set counts to. what exactly does this subquery return "select count(*) from EMPLOYEE e where p.dept_id = e.dept_id"</p>