MySQL, DELETE Query with a Join

Question

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

Answer 1

Should be:

DELETE noteproject FROM noteproject 
INNER JOIN ...

Alternatively, you can write:

DELETE FROM noteproject USING noteproject
INNER JOIN ...

Answer 2

I have no db at the moment to test what I'm saying but here's a reference to the mysql docs where your case is taken as an example:

You can specify multiple tables in a DELETE statement to delete rows from one or more tables depending on the particular condition in the WHERE clause.

And also:

1) For the first multiple-table syntax, only matching rows from the tables listed before the FROM clause are deleted.
2)For the second multiple-table syntax, only matching rows from the tables listed in the FROM clause (before the USING clause) are deleted.
The effect is that you can delete rows from many tables at the same time and have additional tables that are used only for searching:

1)
DELETE t1, t2 
FROM t1 INNER JOIN t2 INNER JOIN t3
WHERE t1.id=t2.id AND t2.id=t3.id;

2)
DELETE FROM t1, t2 
USING t1 INNER JOIN t2 INNER JOIN t3
WHERE t1.id=t2.id AND t2.id=t3.id;

These statements use all three tables when searching for rows to delete, but delete matching rows only from tables t1 and t2.

Answer 3

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.

Use:

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

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

Answer 4

Try in the WHERE part

tblname.noteID = '#attributes.noteID#' AND tblname.personID = '#attributes.personID#'

Answer 5

Tested with MySQL 5.1.41:

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

Explanation:

• You need to qualify noteID and personID with table names or table aliases in the WHERE clause, or else MySQL complains that they're ambiguous (because the column exists in more than one table).
• Check the syntax for multi-table DELETE. There are a couple of different forms but you weren't matching any of the legal syntax forms.