title: Randomly selecting rows and updating another table per row in Postgres
body: <p>I'm using postgres 10 and I'm looking to randomise some data.</p>
  <p>I start by creating a temporary table and fill it with 1,000 rows of random data.</p>
  <p>I then want to merge that into another table that may have less or more rows than the random data.</p>
  <p>For each row in my dimension table I want to select a random row from the random data in the temporary table, setting the values in the dimension table to the randomly selected rows values in the temporary table.</p>
  <p>eg.</p>
  <p>I have a table called <strong>reference.tv_shows</strong> with the fields <strong>Name</strong> and <strong>Category</strong>.</p>
  <p>I have a temporary table called <strong>random_tv_shows</strong> with the fields <strong>Name</strong> and <strong>Category</strong>. This data is completely random and consists of 1,000 rows.</p>
  <p>I want to go through EACH row in the <strong>reference.tv_shows</strong> and pick a random row in the <strong>random_tv_shows</strong> table and set the <strong>reference.tv_shows</strong> Name and Category to be that of the selected row in <strong>random_tv_shows</strong>.</p>
  <p>I tried running a fairly simple select but it looks as though it evaluates itself once then updates (Or maybe RANDOM() is only random once per TX?).</p>
  <pre><code>UPDATE reference.tv_shows SET &quot;Name&quot; = (SELECT &quot;Name&quot; FROM random_tv_shows ORDER BY RANDOM() LIMIT 1)
  </code></pre>
  <p>Is there a way to do this in postgres?</p>