问题描述
我有以下表格:
和:
我制定了这个查询:
Update Table 1 SET DY_H_ID = ( SELECT MAX(ID) FROM Table 2 WHERE H_DateTime <= DY_Date AND H_IDX = DY_IDX AND H_HA_ID = 7 AND H_HSA_ID = 19 AND H_Description LIKE 'Diary item added for :%' ) WHERE DY_H_ID IS NULL AND DY_IDX IS NOT NULL
结果如下:
但是,此查询会更新所有 6 行.我只需要用最新日期更新两行,即 '2013-08-29 15:00:00.000'.这意味着 6 条记录中只有 2 条会被更新,另外 4 条将保持为 NULL.
However, this query updates all 6 rows. I need to update only the two rows with the latest date, that would be '2013-08-29 15:00:00.000'. That would mean only 2 of the 6 records would be updated and the other 4 would remain NULL.
如何通过添加到上述查询来做到这一点?我知道这可能并不理想,但别无选择,只能做这样的事情.我不明白的是你如何只选择最新的日期而不对其进行硬编码.此数据可能会发生变化,并且不会总是相同的日期等.
How can I do this by adding to the above query? I know this might not be ideal but there is no option but to do something like this. What I don't understand is how do you select only the latest dates without hardcoding it. This data can change and it won't always be the same dates etc.
推荐答案
试试这个:
UPDATE TABLE 1 SET DY_H_ID = (SELECT Max(ID) FROM TABLE 2 WHERE H_DATETIME <= DY_DATE AND H_IDX = DY_IDX AND H_HA_ID = 7 AND H_HSA_ID = 19 AND H_DESCRIPTION LIKE 'Diary item added for :%') WHERE DY_H_ID IS NULL AND DY_IDX IS NOT NULL AND DY_DATE = (SELECT Max(DY_DATE) FROM TABLE1)