title: Assign a variable a dynamic value in SQL in Databricks / Spark
body: <p>I feel like I must be missing something obvious here, but I can't seem to dynamically set a variable value in Spark SQL.</p>
  
  <p>Let's say I have two tables, <code>tableSrc</code> and <code>tableBuilder</code>, and I'm creating <code>tableDest</code>.</p>
  
  <p>I've been trying variants on </p>
  
  <pre class="lang-sql prettyprint-override"><code>SET myVar FLOAT = NULL
  
  SELECT
      myVar = avg(myCol)
  FROM tableSrc;
  
  CREATE TABLE tableDest(
      refKey INT,
      derivedValue FLOAT
  );
  
  
  INSERT INTO tableDest
      SELECT
          refKey,
          neededValue * myVar AS `derivedValue`
      FROM tableBuilder
  </code></pre>
  
  <p>Doing this in T-SQL is trivial, in a surprising win for Microsoft (<code>DECLARE</code>...<code>SELECT</code>). Spark, however, throws</p>
  
  <p><code>Error in SQL statement: ParseException: 
  mismatched input 'SELECT' expecting &lt;EOF&gt;(line 53, pos 0)</code></p>
  
  <p>but I can't seem to assign a derived value to a variable for reuse. I tried a few variants, but the closest I got was assigning a variable to a string of a select statement.</p>
  
  <p><a href="https://i.stack.imgur.com/MNLXx.png" rel="noreferrer"><img src="https://i.stack.imgur.com/MNLXx.png" alt="Databricks Screenshot"></a></p>
  
  <p>Please note that this is being adapted from a fully functional script in T-SQL, and so I'd just as soon not split out the dozen or so SQL variables to compute all those variables with Python spark queries just to insert <code>{var1}</code>, <code>{var2}</code>, etc in a multi hundred line f-string. <em>I know how to do this, but it will be messy, difficult, harder to read, slower to migrate, and worse to maintain and would like to avoid this if at all possible</em>.</p>