[SOLVED] How to UPDATE only the "most recent" record while joining several tables in MySQL

Issue

This question has been asked a few different ways on this site, but I can’t seem to figure how to implement an update in this specific case. Generally, I am trying to update the most recent ContactNote for every Contact that belongs to a TeamId and has specific Categories. It’s a fairly straight forward DB setup (see diagram below).

I have successfully created a "select clause" that returns all the records I would like to update, but when I add the UPDATE language, MySQL gives me the error: Error Code: 1093. You can't specify target table 'cn1' for update in FROM clause

Any guidance is appreciated.

Working SELECT Clause

SELECT cn1.* from ContactNote cn1
    INNER JOIN Contact contact on contact.ContactId = cn1.ContactId
    INNER JOIN ContactCategory contactCategory on contactCategory.ContactId = contact.ContactId
    INNER JOIN Category category on category.CategoryId = contactCategory.CategoryId
    INNER JOIN ContactNote contactNote
        ON contactNote.ContactNoteId =
        (SELECT cn2.ContactNoteId
            FROM ContactNote cn2
            WHERE contact.ContactId = cn2.ContactId
            ORDER BY cn2.NoteDateTime DESC
            LIMIT 1
        )
where contact.TeamId = 1
    and contact.SpouseLastName = 'Rhodes'
    and category.`Name` in ('Sphere')
;

Non-Working UPDATE Clause

update ContactNote cn1
    INNER JOIN Contact contact on contact.ContactId = cn1.ContactId
    INNER JOIN ContactCategory contactCategory on contactCategory.ContactId = contact.ContactId
    INNER JOIN Category category on category.CategoryId = contactCategory.CategoryId
    INNER JOIN ContactNote contactNote
        ON contactNote.ContactNoteId =
        (SELECT cn2.ContactNoteId
            FROM ContactNote cn2
            WHERE contact.ContactId = cn2.ContactId
            ORDER BY cn2.NoteDateTime DESC
            LIMIT 1
        )
SET cn1.IsProspecting = b'1' 
where contact.TeamId = 1
    and contact.SpouseLastName = 'Rhodes'
    and category.`Name` in ('Sphere')
;

Solution

MySQL generally doesn’t like it when you SELECT from the table in a subquery and try to UPDATE it in the same statement.

A workaround is to join to the table instead of selecting in a subquery. We’re looking for cases where there is no matching row with a greater date:

update ContactNote cn
    INNER JOIN Contact contact on contact.ContactId = cn.ContactId
    INNER JOIN ContactCategory contactCategory on contactCategory.ContactId = contact.ContactId
    INNER JOIN Category category on category.CategoryId = contactCategory.CategoryId
    LEFT OUTER JOIN ContactNote gt
        ON gt.ContactId = cn.ContactId AND gt.NodeDateTime > cn.NodeDateTime
SET cn.IsProspecting = b'1' 
WHERE contact.TeamId = 1
    AND contact.SpouseLastName = 'Rhodes'
    AND category.`Name` IN ('Sphere')
    AND gt.ContactId IS NULL -- meaning there is no note with a greater datetime
;

LEFT OUTER JOIN returns NULL for all columns of the joined table, if there is no matching row found. If the condition is trying to find notes with a greater NodeDateTime, and none is found, then the NodeDateTime in the row cn must be the one with the greatest (most recent) datetime for the respective conctact.

This does have one weakness: what if there are multiple notes tied for most recent? It would update all those for which there is no note with a greater datetime, which might update multiple notes. Is this what you want?

Answered By – Bill Karwin

Answer Checked By – Katrina (BugsFixing Volunteer)