2013-10-31, 08:19 PM
For some reason my DB contains a lot of orphaned post. I tried to execute this:
DELETE FROM mybb_posts LEFT JOIN mybb_threads ON mybb_posts.tid = mybb_threads.tid WHERE mybb_threads.tid IS NULL
but "#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'LEFT JOIN mybb_threads on mybb_posts.tid=mybb_threads.tid WHERE mybb_threads.tid' at line 1"
What's wrong with the query? Doing a SELECT * with the same query does work (and returns almost 7000 posts).
solution:
DELETE FROM mybb_posts
WHERE mybb_posts.tid NOT IN (
SELECT tid FROM mybb_threads
)
DELETE FROM mybb_posts LEFT JOIN mybb_threads ON mybb_posts.tid = mybb_threads.tid WHERE mybb_threads.tid IS NULL
but "#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'LEFT JOIN mybb_threads on mybb_posts.tid=mybb_threads.tid WHERE mybb_threads.tid' at line 1"
What's wrong with the query? Doing a SELECT * with the same query does work (and returns almost 7000 posts).
solution:
DELETE FROM mybb_posts
WHERE mybb_posts.tid NOT IN (
SELECT tid FROM mybb_threads
)