How to correctly compose a specific SQL query for the site database on DLE?

Question

MySQL

Hello everyone, there is a site on DLE. There are tables dle_comments and dle_post .
A request or a combination of requests is needed to delete those comments for which there is no news already (this happened because news was sometimes deleted not through the admin panel, but directly from the database).
So you need to take the post_id field from dle_comments , which is compared with the dle_post table with the id field. If there are matches, then we leave it unchanged, if not, then we delete the entire line from the dle_comments table.

Answer 1

You can use WHERE NOT EXISTS as suggested by Akina

DELETE FROM dle_comments 
WHERE NOT EXISTS (SELECT 1 FROM dle_post WHERE dle_post.id = dle_comments.post_id);

or you can LEFT JOIN

DELETE dle_comments.*
FROM dle_comments 
LEFT JOIN dle_post ON dle_post.id = dle_comments.post_id
WHERE  dle_post.id IS NULL;

Check SQL online