title: Updating column value using query & join
body: <p>I'm attempting to establish a date when a transaction actually occurred in my DB since the system used tracks by transaction ID and not a date. The following query is what I've attempted, which ran, but did not update any values:</p>
  
  <pre><code>UPDATE dbo.transactions
  SET t.time_ran=e.time_ran
  FROM transactions t
  CROSS JOIN eod_master e
  WHERE(t.clinic=e.clinic) AND (t.tran_num BETWEEN e.start_tran_num AND e.end_tran_num)
  </code></pre>
  
  <p>I have tested the following query to make sure that the value I am looking for is correct without any issues:</p>
  
  <pre><code>SELECT t.tran_num, t.clinic, e.time_ran, t.date_entered, t.clinic
  FROM transactions t
  CROSS JOIN eod_master e
  WHERE(t.clinic=e.clinic) AND (t.tran_num BETWEEN e.start_tran_num AND e.end_tran_num)
  </code></pre>
  
  <p>Since there weren't any errors I'm not sure why the values aren't being updated. I did run into an error with my <code>t.time_ran</code>throwing an error <code>The multi-part identifier "t.time_ran" could not be bound.</code> But I got around it by getting rid of the alias for that table when testing.</p>
  
  <p>UPDATE:</p>
  
  <p>Just to clarify how the tables are setup. I have 35 different <code>clinic</code> values and each of them have a <code>start_tran_num</code> and <code>end_tran_num</code> for each day of business. <code>time_ran</code> is the <code>datetime</code> of when the end of day was established. The start and end declarations won't overlap at the same clinic, IE: day one starts at 1, ends at 200, day 2 automatically starts at 201, etc.</p>
  
  <p><a href="https://i.stack.imgur.com/bXBua.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/bXBua.jpg" alt="enter image description here"></a></p>
  
  <p>Here is a mock-up example of what the data is like. I've attempted the following code after all the provided insight without any changes. There are no NULL values in the <code>eod_master.time_ran</code> column and currently all <code>transations.time_ran</code> are null so the query shouldn't be attempting to put in the same values that already exist.</p>
  
  <pre><code>UPDATE transactions
  SET time_ran=e.time_ran
  FROM transactions t
  INNER JOIN eod_master e
  ON e.clinic=t.clinic
  WHERE (t.tran_num BETWEEN e.start_tran_num AND e.end_tran_num)
  </code></pre>