title: MySQL Unique Constraint, on Update, set exiting to NULL
body: <p>I am sure there is a way to search for this to find an existing answer, but most of the results deal with either enforcing a unique column by blocking the update or doing a swap of unique values between two rows (which apparently is broken in MySQL).  I don't want to swap I just want the row that currently has the unique value to be updated to <code>NULL</code>.</p>
  
  <p>Here is how I could do it with two queries (with a two column table where the second column, person_id must be unique):</p>
  
  <pre><code>UPDATE bathroom_occupant
  SET person_id = NULL
  WHERE person_id = '$person_id';
  
  UPDATE bathroom_occupant
  SET person_id = '$person_id' 
  WHERE bathroom_id = '$new_bathroom_id';
  </code></pre>
  
  <p>The above removes the <code>person_id</code> from it's current <code>bathroom_id</code> pairing and then places the <code>person_id</code> with it's new <code>bathroom_id</code> pairing. What I would like is to have only one query, which either uses some form of <code>COALESCE</code> to set the one row to NULL and the other to the <code>person_id</code> or use some table-level rule, similar I would think to a unique constraint, where running the second query alone automatically sets the column value to NULL for any other row already set to the unique value.</p>
  
  <p>To use a simpler example: I know that a unique constraint can be used to prevent a student from enrolling in two sections of the same class. But rather than prevent by blocking, is it possible to prevent by "moving"?</p>