title: Dynamically update table with column from another table
body: <p>I have a table customer like this:</p>
  
  <pre><code>CREATE TABLE tbl_customer (
    id INTEGER,
    name VARCHAR(16),
    voucher VARCHAR(16)
  );
  </code></pre>
  
  <p>and a voucher table like this:</p>
  
  <pre><code>CREATE TABLE tbl_voucher (
    id INTEGER,
    code VARCHAR(16)
  );
  </code></pre>
  
  <p>Now imagine that the customer table always has rows with id and name filled in, however the voucher needs to be inserted periodically from the <code>tbl_voucher</code> table. </p>
  
  <p>Important: every voucher may only be assigned to one specific customer (i.e. must be unique)</p>
  
  <p>I wrote a query like this:</p>
  
  <pre><code>UPDATE tbl_customer
  SET voucher = (
      SELECT code
      FROM tbl_voucher
      WHERE code NOT IN (
          SELECT voucher
          FROM tbl_customer
          WHERE voucher IS NOT NULL
      )
      LIMIT 1
  )
  WHERE voucher IS NULL;
  </code></pre>
  
  <p>However this is not working as expected, since the part that looks for an unused voucher is executed once and said voucher is then applied to every customer.</p>
  
  <p>Any ideas on how I can solve this without using programming structures such as loops?</p>
  
  <p>Also, some example data so you can imagine what I would like to happen:</p>
  
  <pre><code>INSERT INTO tbl_customer VALUES (1, 'Sara', 'ABC');
  INSERT INTO tbl_customer VALUES (1, 'Simon', 'DEF');
  INSERT INTO tbl_customer VALUES (1, 'Andy', NULL);
  INSERT INTO tbl_customer VALUES (1, 'Alice', NULL);
  
  INSERT INTO tbl_voucher VALUES (1, 'ABC');
  INSERT INTO tbl_voucher VALUES (2, 'LOL');
  INSERT INTO tbl_voucher VALUES (3, 'ZZZ');
  INSERT INTO tbl_voucher VALUES (4, 'BBB');
  INSERT INTO tbl_voucher VALUES (5, 'CCC');
  </code></pre>
  
  <p>After the wanted query is executed, I'd expect <code>Andy</code> to have the voucher <code>LOL</code> and <code>Alice</code> should get <code>ZZZ</code></p>