title: How do I update a table with the max value of a different table?
body: <p>I need to update col1 in table1 with the highest value in val in table2 that is lower than
  col2 in table1.  I am trying this:</p>
  
  <pre><code>
      update table1 set col1 = max(t2.val)
      from table2 t2 where t2.VAL &lt; col2</code></pre>
  
  <p>I am getting this error:
  An aggregate may not appear in the set list of an UPDATE statement.</p>
  
  <p>Here is sample data</p>
  
  <pre><code>
  Table1
  Col1    Col2
  2   null
  3   null
  4   null
  6   null
  7   null
  8   null
  9   null
  10  null
  
  Table2
  Val
  1
  5
  
  </code></pre>
  
  <p>after I run the query I would want it to look like this:</p>
  
  <pre><code>
  Table1
  Col1    Col2
  2   1
  3   1
  4   1
  6   5
  7   5
  8   5
  9   5
  10  5
  </code></pre>