MySQL，使用联接删除查询 [英] MySQL, DELETE Query with a Join

问题描述

有人可以帮助我了解此查询的问题吗

Can someone help me understand what's wrong with this query:

DELETE FROM noteproject 
INNER JOIN note ON noteproject.noteID = note.noteID
INNER JOIN person ON note.personID = person.personID
WHERE noteID = '#attributes.noteID#' 
  AND personID = '#attributes.personID#'

推荐答案

您尝试使用的内容不起作用的原因是，

The reason why what you are trying to use doesn't work, is because MySQL doesn't support join syntax in the delete statement in the manner you tried.

使用:

DELETE FROM NOTEPROJECT
 WHERE noteID = '#attributes.noteID#' 
   AND note_id IN (SELECT n.note_id 
                     FROM NOTE n
                    WHERE n.personID = '#attributes.personID#')

...或使用EXISTS:

...or using EXISTS:

DELETE FROM NOTEPROJECT
 WHERE noteID = '#attributes.noteID#' 
   AND EXISTS (SELECT NULL
                 FROM NOTE n
                WHERE n.note_id = note_id
                  AND n.personID = '#attributes.personID#')

这篇关于MySQL，使用联接删除查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！