помогите пожалуйста с запросом

[silvereye]

боты заспамили весь форум, начал удалять спам, остались пустые топики.. нашел такой запрос для их удаления

DELETE FROM phpbb_topics AS t WHERE 0 = ( SELECT count(post_id) FROM phpbb_posts AS p WHERE t.topic_id = p.topic_id );

но он не работает, пишет : #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 'WHERE 0 = ( SELECT count(post_id) FROM phpbb_posts AS p WHERE t.topic_id = p.top' at line 1

Версия MySQL-клиента: 5.1.22-rc

[Serge_Bliznykov]

попробуйте сначала такой запрос:

SELECT * FROM phpbb_topics AS t WHERE  t.topic_id IN ( SELECT p.topic_id FROM phpbb_posts AS p HAVING count(post_id)=0 GROUP BY  p.topic_id);

я его набросал прямо здесь, не проверяя. Если всё отработает (он должен вернуть пустые топики)
тогда можете удалять:

DELETE FROM phpbb_topics AS t WHERE  t.topic_id IN ( SELECT p.topic_id FROM phpbb_posts AS p HAVING count(post_id)=0 GROUP BY  p.topic_id);

p.s. хотя я бы предварительно всё равно сделал backup...
на всякий случай ;-)

[silvereye]

к сожалению запрос - SELECT * FROM phpbb_topics AS t WHERE t.topic_id IN ( SELECT p.topic_id FROM phpbb_posts AS p HAVING count(post_id)=0 GROUP BY p.topic_id);

вернул ошибку - #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 'GROUP BY p . topic_id )
LIMIT 0, 30' at line 1

[Serge_Bliznykov]

а попробуйте переставить местами having и group by
сначал такой запрос:

SELECT p.topic_id FROM phpbb_posts p GROUP BY p.topic_id HAVING count(post_id)=0

если пройдёт, тогда этот:

SELECT * FROM phpbb_topics AS t WHERE  t.topic_id IN (SELECT p.topic_id FROM phpbb_posts p GROUP BY p.topic_id HAVING count(post_id)=0)

[Domovoy]

Я б зделал так

DELETE FROM phpbb_topics 
WHERE not exists(SELECT * FROM phpbb_posts WHERE phpbb_topics.topic_id =topic_id);