Updating only ID's with the latest date SQL (2

问题:

I have the following tables:

and:

And I formulated this query:

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

which results in this:

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.

回答1:

Try this:

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) 

回答2:

Just add another condition to where:

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 Table 1)

回答3:

using CTE, compatible with SQL-Server-2005 or later

;WITH cte AS (
    SELECT H_IDX, MAX(ID) MaxID
    FROM Table2
    WHERE H_HA_ID = 7 
          AND H_HSA_ID = 19 
          AND H_Description LIKE 'Diary item added for :%'
    GROUP BY H_IDX
)
Update Table1 
SET DY_H_ID = MaxID
FROM Table1 
INNER JOIN CTE ON cte.H_IDX = DY_IDX
WHERE DY_H_ID IS NULL