How to UPDATE only the "most recent" record while joining several tables in MySQL-CodePudding

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')
;

CodePudding user response：

First there is no point on using ContactNote twice.

This

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
        )

could be transformed in

INNER JOIN 
          (  SELECT cn2.ContactNoteId
             FROM ContactNote cn2
             WHERE contact.ContactId = cn2.ContactId
             ORDER BY cn2.NoteDateTime DESC
             LIMIT 1
          ) as t1 on cn1.ContactNoteId=t1.ContactNoteId

Not tested, this might work:

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 
          (  SELECT cn2.ContactNoteId
             FROM ContactNote cn2
             WHERE contact.ContactId = cn2.ContactId
             ORDER BY cn2.NoteDateTime DESC
             LIMIT 1
          ) as t1 on cn1.ContactNoteId=t1.ContactNoteId 
SET cn1.IsProspecting = b'1' 
where contact.TeamId = 1
and contact.SpouseLastName = 'Rhodes'
and category.`Name` in ('Sphere') ;

Chek for more details https://dev.mysql.com/doc/refman/8.0/en/update.html

CodePudding user response：

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
;