I have a SELECT query with multiple JOIN who works perfectly, but now I want to DELETE this rows, and I can't find how. This code returns me an error SQL (1064):
DELETE FROM rapport
INNER JOIN course ON rapport.course_id = course.id
INNER JOIN reunion ON course.reunion_id = reunion.id
INNER JOIN paris ON rapport.paris_id = paris.id
WHERE reunion.id = 231431
AND paris.bookmaker_id = 3
I need some help, thanks
Your syntax is wrong.
Use this with aliases also:
DELETE r
FROM rapport r
INNER JOIN course c ON r.course_id = c.id
INNER JOIN reunion u ON c.reunion_id = u.id
INNER JOIN paris p ON r.paris_id = p.id
WHERE u.id = 231431 AND p.bookmaker_id = 3
If you want to delete from all tables then you must specify their aliases after DELETE:
DELETE r, c, u, p
FROM rapport r
INNER JOIN course c ON r.course_id = c.id
INNER JOIN reunion u ON c.reunion_id = u.id
INNER JOIN paris p ON r.paris_id = p.id
WHERE u.id = 231431 AND p.bookmaker_id = 3
Basically, you need to specify what table to delete from. Assuming this is rapport:
DELETE r
FROM rapport r JOIN
course c
ON r.course_id = c.id JOIN
reunion ru
ON c.reunion_id = ru.id JOIN
paris p
ON r.paris_id = p.id
WHERE ru.id = 231431 AND p.bookmaker_id = 3;
MySQL also supports deletion from multiple tables. However, cascading constraints are usually a better approach.
