title: Update column based on duplicate columns
body: <p>I have 2 tables:</p>
  <p><strong>account</strong></p>
  <pre><code>+----+---------------------+
  | Id |        Email        |
  +----+---------------------+
  |  1 | &quot;test@example.com&quot;  |
  |  2 | &quot;test2@example.com&quot; |
  |  3 | &quot;test3@example.com&quot; |
  |  4 | &quot;test@example.com&quot;  |
  +----+---------------------+
  </code></pre>
  <p><strong>character</strong></p>
  <pre><code>+----+-----------+
  | Id | AccountId |
  +----+-----------+
  |  1 |         1 |
  |  2 |         1 |
  |  3 |         2 |
  |  4 |         3 |
  |  5 |         4 |
  +----+-----------+
  </code></pre>
  <p><code>character.AccountId</code> is a FK to <code>account.Id</code>. Both <code>Id</code> columns are PK's to their respective tables.</p>
  <p>I need to update the <code>character</code> table such that the new <code>AccountId</code> matches a row in <code>account</code> with the lowest account's <code>Id</code> but with the same <code>Email</code> as the currently set <code>AccountId</code>.</p>
  <p>For example, in the mock data presented above all accounts have unique emails except account Id <code>1</code> and <code>4</code>, they both share <code>test@example.com</code> as email.</p>
  <p>This means that after the update, the rows in the <strong>character</strong> table should stay the same except for the row with <code>Id = 5</code>, this row has an <code>AccountId = 4</code> and this account shares an email with an account that has a lower account Id, namely Id <code>1</code>. So the result output should be :</p>
  <pre><code>+----+-----------+
  | Id | AccountId |
  +----+-----------+
  |  1 |         1 |
  |  2 |         1 |
  |  3 |         2 |
  |  4 |         3 |
  |  5 |         1 |
  +----+-----------+
  </code></pre>
  <p>After the operation.</p>
  <p>I've got this to work with a <code>TRIGGER BEFORE INSERT</code> on <code>character</code> to check if there are lower accountIds but can't get this to work with a simple <code>UPDATE</code>:</p>
  <pre><code>UPDATE `character` SET `AccountId` = (SELECT MIN(Id) FROM `account` WHERE ?);
  </code></pre>
  <p>I've thought of maybe making a temporary table to store the account Id and the email with a <code>GROUP BY email</code> but this also fails. It being MySQL I can't use <code>MERGE</code> either.</p>