title: Updating fact tables in SQL doesn't work properly
body: <p>I've been working with SQL Server for 4 months now, and most of my work is on building a Datawarehouse for my company. We normally employ the star schema method, but since there are too many dimensions, I'd normally run UPDATE queries to fill in the dimension columns later. Upon retesting my queries, I've run into a bizarre mistake that I absolutely cannot explain and is making me very afraid.
  My data had a <code>Registered_on</code> column typed as DateTime. I created a dimension called HOUR with 24 rows, one for each hour of the day and an int for matching. So, the first row is midnight and has a column with a 0. </p>
  
  <p>When I ran the query of </p>
  
  <pre><code>SELECT HourDimension.Hour
      ,DATEPART(Hour, AdmissionLogs.Registered_on)
  FROM FactTable
  INNER JOIN AdmissionLogs ON AdmissionLogs.ID = FactTable.LogID
  INNER JOIN HourDimension ON HourDimension.HourNumber = DATEPART(Hour, AdmissionLogs.Registered_on)
  </code></pre>
  
  <p>Everything matched perfectly, as per this image. </p>
  
  <p><a href="https://i.stack.imgur.com/8OWNl.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/8OWNl.png" alt="enter image description here"></a></p>
  
  <p>So I did the update query as:</p>
  
  <pre><code>UPDATE FactTable
  SET FactTable.IdfHourDimension = HourDimension.IdfHourDimension
  FROM FactTable
  INNER JOIN AdmissionLogs ON AdmissionLogs.ID = FactTable.LogID
  INNER JOIN HourDimension ON HourDimension.HourNumber = DATEPART(Hour, AdmissionLogs.Registered_on)
  </code></pre>
  
  <p>And upon reviewing the results of the fact table via:</p>
  
  <pre><code>SELECT HourDimension.Hour
      ,HourDimension.HourNumber
      ,DatePart(Hour, AdmissionLogs.Registered_on)
  FROM FactTable
  INNER JOIN HourDimension ON HourDimension.IdfHourDimension = FactTable.IdfHourDimension
  INNER JOIN AdmissionLogs ON AdmissionLogs.ID = FactTable.LogID
  </code></pre>
  
  <p>I got this:</p>
  
  <p><a href="https://i.stack.imgur.com/DVks4.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/DVks4.png" alt="enter image description here"></a></p>
  
  <p>The hours ABSOLUTELY don't match! I don't know what is going on, but I'm seriously doubting everything I know.</p>