問(wèn)題描述

我有以下表格:

和:

我制定了這個(gè)查詢:

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

結(jié)果如下:

但是，此查詢會(huì)更新所有 6 行.我只需要用最新日期更新兩行，即 '2013-08-29 15:00:00.000'.這意味著 6 條記錄中只有 2 條會(huì)被更新，另外 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.

如何通過(guò)添加到上述查詢來(lái)做到這一點(diǎn)?我知道這可能并不理想，但別無(wú)選擇，只能做這樣的事情.我不明白的是你如何只選擇最新的日期而不對(duì)其進(jìn)行硬編碼.此數(shù)據(jù)可能會(huì)發(fā)生變化，并且不會(huì)總是相同的日期等.

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.

推薦答案

試試這個(gè):

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) 

這篇關(guān)于僅使用最新日期 SQL 更新 ID(6 個(gè)中的 2 個(gè))的文章就介紹到這了，希望我們推薦的答案對(duì)大家有所幫助，也希望大家多多支持html5模板網(wǎng)！