title: A join that "allocates" from available rows
body: <p>I have a table X I want to update according to the entries in another table Y.  The join between them is not unique.  However, I want each entry in Y to update a <em>different</em> entry in X.</p>
  
  <p>So if I have table X:</p>
  
  <pre><code>i (unique)  k           v         
  ----------  ----------  ----------
  p           100         b         
  q           101         a         
  r           202         x         
  s           301         a         
  </code></pre>
  
  <p>and table Y:</p>
  
  <pre><code>k (unique)  v         
  ----------  ----------
  0           a         
  1           b         
  2           a         
  3           c         
  4           a         
  </code></pre>
  
  <p>I want to end up with table X like:</p>
  
  <pre><code>i           k           v
  ----------  ----------  ----------
  p           1           b
  q           0           a
  r           202         x
  s           2           a
  </code></pre>
  
  <p>The important result here is that the two rows in X with v = 'a' have been updated to two distinct values of k from Y.  (It doesn't matter which ones.)</p>
  
  <p>Currently, this result is achieved by an extra column and a program roughly like:</p>
  
  <pre><code>UPDATE X SET X.used = FALSE;
  for Yk, Yv in Y:
      UPDATE X
         SET X.k = Yk,
             X.used = TRUE
       WHERE X.i IN (SELECT X.i FROM X
                      WHERE X.v = Yv AND NOT X.used
                      LIMIT 1);
  </code></pre>
  
  <p>In other words, the distinctness is achieved by "using up" the rows in Y.  This doesn't scale well.</p>
  
  <p>(I'm using SQLite3 and Python, but don't let that limit you.)</p>