title: mysql nested SELECT in UPDATE of same table
body: <p>Essentially I need to do something like this.... this is just an example... but the syntax of the first query doesn't work in MySQL</p>
  
  <pre><code>update people set prize = '' 
  where prize = 'Gold' and class = (select class from people where id = person_id);
  update people set prize = 'Gold' where id = &lt;id&gt;;
  </code></pre>
  
  <p>Only one person can have the Gold prize in any class. I only know the person_id of the person who receives the Gold prize.</p>
  
  <p>I am trying to blank out any previous Gold prize winners in the same class as person_id in the first query. Then set the new Gold winner in the second.</p>
  
  <p>I believe I need to use some type of inner join, but I'm not 100% sure on this.</p>
  
  <p>What would be even smarter if I could do the whole lot in one query!</p>
  
  <p>Can anyone lend advice?</p>
  
  <p>Thanks :)</p>