CODEWITHSUNDEEP
mysqlsql
Paul Taylor
Paul Taylor

Reputation: 13110

Why doesnt DELETE based on SELECT work with mysql 5.0

Using Mysql 5.0 this works

select * 
from jforum_topics t1 
where topic_id in ( 
            select topic_id 
            from jforum_posts t2  
            where need_moderate=true
        );

but this doesn't

mysql> delete from jforum_topics t1 
        where topic_id in ( 
                select topic_id 
                from jforum_posts t2  
                where need_moderate=true
            );

ERROR 1064 (42000): 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 topic_id in ( select topic_id from jforum_posts t2 where need_moderate=tr' at line 1

I thought it should work , how else do i do this ?

Upvotes: 0

Views: 46

Answers (1)

Barbaros Özhan
Barbaros Özhan

Reputation: 65288

You can use in or exists without aliasing (in this case t1) :

delete from jforum_topics 
      where topic_id in ( 
                select topic_id 
                  from jforum_posts t2  
                 where need_moderate=true
                );

or

delete from jforum_topics 
      where exists ( 
                select 0
                  from jforum_posts t2  
                 where need_moderate=true
                   and t2.topic_id = jforum_topics.topic_id
                );

or need to alias twice :

delete t1 from jforum_topics t1 
      where topic_id in ( 
                select topic_id 
                  from jforum_posts t2  
                 where need_moderate=true
                );

Upvotes: 2

Related Questions