Query delete with Join mysql

Question

Query delete with Join mysql

Navigation

#1 by (1 votes)
#2 by (1 votes)

2

I have a problem with this query, I do not know what's wrong:

DELETE FROM mdl_forum_discussions WHERE id IN 
(
SELECT mdl_forum_discussions.id 
    FROM mdl_forum_discussions 
    LEFT JOIN mdl_forum_posts ON mdl_forum_discussions.id = mdl_forum_posts.discussion 
    WHERE mdl_forum_discussions.id = 4
);

Return this error:

# 1093 - You can not specify target table 'mdl_forum_discussions' for update in FROM clause

mysql sql

asked by anonymous 14.02.2017 / 18:01

2 answers

1

As @ Kenny has already replied, it is not possible to give the DELETE command at the same time that a SELECT is made. Another solution you can take is:

DELETE A FROM mdl_forum_discussions AS A
LEFT JOIN mdl_forum_posts AS B
  ON (A.id = B.discussion)
WHERE B.id = 4;

Make sure you put the table alias right after the DELETE command, otherwise the records of both tables will be deleted.

14.02.2017 / 19:33

Division by zero in double = result Infinity Catch a selected item in Dropdownlistfor using an ASP.NET MVC ViewModel

score 1 · Accepted Answer

The problem is that you can not in UPDATE , DELETE , or INSERT reference the same table in a subquery, but there is a solution:

DELETE FROM mdl_forum_discussions WHERE id IN 
(
SELECT mdl_forum_discussions.id 
    FROM (SELECT * FROM mdl_forum_discussions) as mfd 
    LEFT JOIN mdl_forum_posts ON mfd.id = mdl_forum_posts.discussion 
    WHERE mfd.id = 4
);