Query delete with Join mysql

Asked

Viewed 1,623 times

2

People I have problem with this query do not know what is 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
);

Returns this error:

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

mysql sql

  • What problem? Do not delete what needed? an exception ? put what is the problem too so it gets half Generic.

  • Add more information, which error is returning, the subquerie alone is working?

  • 1

    Possible duplicate of Update with Select

2 answers

1


The problem is that you can’t in a 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
);

1

As @Kenny has already responded, it is not possible to give the command DELETE at the same time that one makes a SELECT. Another solution you can adopt 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;

Watch out to put the table alias right after the command DELETE, otherwise the records of both tables will be deleted.

Browser other questions tagged mysql sql

You are not signed in. Login or sign up in order to post.