CODEWITHSUNDEEP
t-sqlstored-procedures
Daniela
Daniela

Reputation: 657

Update Max(ID) with inner join on date field

I have a stored procedure that will update multiple rows where there is a match between two tables on multiple data columns.

It is possible for there to be multiple matches in the TD table, and I would only like to update the MAX(TID) record in the TD table. I have tried this a few different ways, and I am either getting an update on all matches or nothing updates.

The join is using a Date of Birth and a few other columns in order to find a match. I need to update the SubID and a few other fields where I find a match.

What is the best and most efficient way to do this? 

Update TD
       SET     
       TD.SubID = NM.SubscriberID,  
       TD.EffectiveDate = NM.EffectiveDate,
       TD.LastUpdate = GETDATE()
       FROM TD
        INNER JOIN NM
        ON TD.DateofBirth = NM.DateofBirth
        WHERE       
        TD.MemberFirst = NM.MemberFirst
        AND TD.MemberLast = NM.MemberLast   
        AND TID IN (     
                        SELECT MAX(TID)
                        FROM TD td2             
                        INNER JOIN NM nm2
                        ON td2.DateofBirth = nm2.DateofBirth
                        WHERE td2.MemberFirst = nm2.MemberFirst
                        AND td2.MemberLast = nm2.MemberLast 
                );

The results of this = nothing updated. What do I need to do to update only the MAX(TID) record?

Upvotes: 0

Views: 89

Answers (2)

Tab Alleman
Tab Alleman

Reputation: 31785

If you're interested, the reason your posted code isn't working is because your subquery isn't correlated at all to your outer query.

You say that nothing gets updated, but in fact, one row should get updated: The row with the MAX(TID) in the entire table. Maybe it only looks like that row isn't getting updated because it already has the values that your code would change it to.

Your code should work if you simply change the last clause to this:

    AND TID = (     
                    SELECT MAX(TID)
                    FROM TD td2          
                    WHERE  td2.DateofBirth = NM.DateofBirth
                    AND td2.MemberFirst = NM.MemberFirst
                    AND td2.MemberLast = NM.MemberLast 
            );

Upvotes: 1

Zohar Peled
Zohar Peled

Reputation: 82514

Not sure about most efficient but I would use a common table expression to get the max(tid) for each combination of DateofBirth, MemberFirst and MemberLast and update the table joined to the cte:

WITH CTE AS
(
    SELECT TD.DateofBirth, TD.MemberFirst, TD.MemberLast, MAX(TID) As MaxTID
    FROM TD 
    INNER JOIN NM 
    ON TD.DateofBirth = NM.DateofBirth
    WHERE TD.MemberFirst = NM.MemberFirst
    AND TD.MemberLast = NM.MemberLast 
    GROUP BY TD.DateofBirth, TD.MemberFirst, TD.MemberLast
)

UPDATE T
SET SubID = NM.SubscriberID,  
    EffectiveDate = NM.EffectiveDate,
    LastUpdate = GETDATE()
FROM TD As T
JOIN CTE 
    ON T.DateofBirth = CTE.DateofBirth
    AND T.MemberFirst = CTE.MemberFirst
    AND T.MemberLast = CTE.MemberLast
    AND T.TID = CTE.MaxTID

Upvotes: 1

Related Questions