title: update records in a union select query
body: <p>I've two tables with events that I want to union and sort by date and I need a value in the result that I can group the records by for reporting - in this case <code>code2</code></p>
  
  <pre><code>CREATE TABLE #tbl1 (code1 INT, codeDate DATETIME, code2 INT)
  CREATE TABLE #tbl2 (code1 INT, codeDate DATETIME, code2 INT )
  
  INSERT INTO #tbl1 VALUES( 1, '01 jan 2013 12:00:00', 123)
  INSERT INTO #tbl1 VALUES( 2, '01 jan 2013 14:00:00', 123)
  INSERT INTO #tbl1 VALUES( 1, '01 jan 2013 15:00:00', 234)
  INSERT INTO #tbl1 VALUES( 2, '01 jan 2013 18:00:00', 234)
  
  INSERT INTO #tbl2 VALUES( 10, '01 jan 2013 12:10:00', 0)
  INSERT INTO #tbl2 VALUES( 20, '01 jan 2013 13:20:00', 0)
  INSERT INTO #tbl2 VALUES( 10, '01 jan 2013 15:10:00', 0)
  INSERT INTO #tbl2 VALUES( 20, '01 jan 2013 16:20:00', 0)
  
  SELECT * FROM #tbl1 UNION SELECT * FROM  #tbl2  ORDER BY CODEDATE
  </code></pre>
  
  <p>Returns</p>
  
  <pre><code>code1   codeDate                    code2
  1       2013-01-01 12:00:00.000     123
  10      2013-01-01 12:10:00.000     0
  20      2013-01-01 13:20:00.000     0
  2       2013-01-01 14:00:00.000     123
  1       2013-01-01 15:00:00.000     234
  10      2013-01-01 15:10:00.000     0
  20      2013-01-01 16:20:00.000     0
  2       2013-01-01 18:00:00.000     234
  </code></pre>
  
  <p>Id like to get the values in the <code>code2</code> column to be updated so the tbl2 records that fall between the date values in tbl1 have the <code>code2</code> value from tbl1. (rows 2,3,6 &amp; 7 in results)  eg:</p>
  
  <pre><code>code1   codeDate                    code2
  1       2013-01-01 12:00:00.000     123
  10      2013-01-01 12:10:00.000     123
  20      2013-01-01 13:20:00.000     123
  2       2013-01-01 14:00:00.000     123
  1       2013-01-01 15:00:00.000     234
  10      2013-01-01 15:10:00.000     234
  20      2013-01-01 16:20:00.000     234
  2       2013-01-01 18:00:00.000     234
  </code></pre>
  
  <p>Is this possible with a <code>UNION</code> or do i need a different approach?</p>