Invision Power Services > Silly SQL problem

Help - Search - Members - Calendar

Full Version: Silly SQL problem

Invision Power Services > Community Forums > Community Web Design and Coding

Antony

Aug 1 2006, 10:18 PM

SQL

SELECT * FROM ibf_topics LEFT JOIN ibf_posts ON ibf_topics.tid=ibf_posts.topic_id WHERE ibf_posts.pid IS NULL

gets me all the topics without posts on my board.

But running

SQL

DELETE FROM ibf_topics LEFT JOIN ibf_posts ON ibf_topics.tid=ibf_posts.topic_id WHERE ibf_posts.pid IS NULL

gives me the following error:

QUOTE

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 ibf_posts ON ibf_topics.tid=ibf_posts.topic_id WHERE

How can I delete those rows?

Am I missing the obvious?

bılʞ

Aug 1 2006, 10:24 PM

Can you join on DELETE queries? I didn't think you could.

Antony

Aug 1 2006, 10:29 PM

QUOTE(Sam Adamson @ Aug 1 2006, 11:24 PM)

Can you join on DELETE queries? I didn't think you could.

I'm beginning to think you can't also, but I can't see another way of doing this really.

bılʞ

Aug 1 2006, 10:54 PM

What are you trying to do? Delete all topics without posts?

Antony

Aug 1 2006, 10:57 PM

QUOTE(Sam Adamson @ Aug 1 2006, 11:54 PM)

What are you trying to do? Delete all topics without posts?

Yes.

bılʞ

Aug 1 2006, 11:01 PM

You'll have to get all those topic IDs and just run a looped DELETE query I imagine.

Kevin.LP

Aug 2 2006, 12:11 AM

Yeah, you can not JOIN a delete query, Just loop it as above post

Antony

Aug 2 2006, 12:18 AM

I worked out a good way of doing this quickly - use an UPDATE query to move them to a new forum and then just DELETE all the topics in that forum.

This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.