如何在MySQL中连接多个表时只更新“最近”的记录

这个问题在这个网站上被问了几个不同的方法,但我似乎不知道如何在这个特定的情况下实现更新。一般来说,我试图更新最新的 ContactNote 每 Contact 那是属于一个 TeamId 并且有具体的 Categories .这是一个相当直接的数据库设置(见下图)。

我已经成功创建了一个“select子句”,它返回我想要更新的所有记录,但是当我添加 UPDATE 语言,MySQL给了我一个错误: Error Code: 1093. You can't specify target table 'cn1' for update in FROM 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')
;

非工作更新子句

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