Как ОБНОВИТЬ только «самую последнюю» запись при объединении нескольких таблиц в MySQL
Этот вопрос задавался на этом сайте несколькими способами, но я не могу понять, как реализовать обновление в этом конкретном случае. Как правило, я пытаюсь обновить самые последние ContactNote для каждого Contact, который принадлежит TeamId и имеет определенные Categories. Это довольно простая установка БД (см. диаграмму ниже).
Я успешно создал «предложение выбора», которое возвращает все записи, которые я хотел бы обновить, но когда я добавляю язык UPDATE, MySQL выдает ошибку: Error Code: 1093. You can't specify target table 'cn1' for update in FROM clause
Любое руководство приветствуется.
Рабочий оператор SELECT
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
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')
;
Ответы 1
MySQL обычно не любит, когда вы выбираете из таблицы в подзапросе и пытаетесь ОБНОВИТЬ его в том же операторе.
Обходной путь — присоединиться к таблице вместо выбора в подзапросе. Мы ищем случаи, когда нет соответствующей строки с большей датой:
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 возвращает NULL для всех столбцов объединенной таблицы, если не найдена соответствующая строка. Если условие пытается найти заметки с большим значением NodeDateTime, но ничего не найдено, то NodeDateTime в строке cn должен быть заметкой с самой большой (самой последней) датой и временем для соответствующего контакта.
У этого есть один недостаток: что, если есть несколько заметок, привязанных к последним? Он обновит все те, для которых нет заметки, с большей датой и временем, что может обновить несколько заметок. Это то, что вы хотите?