MySQL，使用联接删除查询

一尘不染

MySQL，使用联接删除查询

sql

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

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#'

阅读 112

2021-03-10

共1个答案

一尘不染

之所以无法使用，是因为MySQL不按照您尝试的方式在delete语句中支持连接语法。

使用：

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

…或使用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#')

2021-03-10