title: SQL UPDATE based on COUNT
body: <p>I have a table that looks as follows</p>
  
  <pre><code>ID   |   action   |  flag
  1    |   A        |  1
  1    |   A        |  1
  1    |   B        |  1
  2    |   A        |  1
  2    |   A        |  1
  2    |   B        |  1
  2    |   B        |  1
  </code></pre>
  
  <p>I want to do the following: If for the same <em>ID</em> the value <em>B</em> in the <em>action</em> column appears more than <em>1 time</em>, then I want to set the <em>flag</em> column for this <em>ID</em> to <em>0</em>.</p>
  
  <p>The result should look like this:</p>
  
  <pre><code>ID   |   action   |  flag
  1    |   A        |  1
  1    |   A        |  1
  1    |   B        |  1
  2    |   A        |  0
  2    |   A        |  0
  2    |   B        |  0
  2    |   B        |  0
  </code></pre>
  
  <p>I know two ways to do this:</p>
  
  <ul>
  <li>Use a subquery: However, I don't want to use a subquery, because I deal with large tables, and a subquery deteriorates performance</li>
  <li>Use a temporary lookup table: I create a temporary lookup table, in which I store the <em>IDs</em> which have value <em>B</em> in the <em>action</em> colum more than <em>1 time</em>, and I then <code>join</code> the temporary table with the original table to find the <em>IDs</em> for which I will set <em>flag</em> to <em>0</em></li>
  </ul>
  
  <p>Is there another option besides the two explained above? Ideally in one query (without subquery and without temporary lookup table). I was thinking about something like a <code>JOIN</code> where the <code>JOIN</code> clause contains something like a <code>GROUP BY</code> and <code>HAVING</code>, but I wasn't successful until now..</p>