在SQL中更新事实表不能正常工作

Question

我已经使用SQL Server 4个月了，我的大部分工作是为我的公司建立一个数据仓库。我们通常使用星型模式方法，但是因为有太多的维度，所以我通常会运行UPDATE查询来填充维度列。在重新测试我的查询时，我遇到了一个我绝对无法解释的奇怪的错误，这让我非常害怕。我的数据有一个类型为DateTime的Registered_on列。我创建了一个名为HOUR的维度，有24行，一天中的每个小时一行，一个int用于匹配。因此，第一行是午夜，并且有一列带有0。

当我运行以下查询时

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)

根据这张图片，一切都很完美。

​

​

因此，我执行了更新查询，如下所示：

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)

并且在通过以下方式查看事实表的结果时：

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

我得到了这个：

​

​

时间绝对不匹配！我不知道发生了什么，但我真的很怀疑我所知道的一切。

Answer 1

The results are different because you are not asking the same SELECT.

In the original query in which "Everything Matches Perfectly" you have ...
INNER JOIN HourDimension
 ON HourDimension.HourNumber = DATEPART(Hour, AdmissionLogs.Registered_on)

In the later query, after the UPDATE you have ...
INNER JOIN HourDimension
 ON HourDimension.IdfHourDimension = FactTable.IdfHourDimension


It should read...
SELECT HourDimension.Hour
    ,HourDimension.HourNumber
    ,DatePart(Hour, AdmissionLogs.Registered_on)
FROM FactTable
INNER JOIN AdmissionLogs
 ON AdmissionLogs.ID = FactTable.LogID
INNER JOIN HourDimension
 ON HourDimension.HourNumber = FactTable.IdfHourDimension