CODEWITHSUNDEEP
mysql
randombits
randombits

Reputation: 48490

MySQL subquery to delete rows

I'm having trouble executing a subquery in order to delete some specific rows in my application. Here's my wrong SQL:

   delete from player_news_items 
     where id IN 
      (select id from player_news_items 
      where player_id NOT IN (select id from players));

What's the right way to write this query?

Upvotes: 0

Views: 45

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271151

In MySQL, you cannot refer to the table being modified in an update or delete, unless you use a join. However, I think you might just want this:

delete pni
    from player_news_items pni
    where player_id NOT IN (select id from players));

As a note, I would advice writing this with not exists:

delete pni
    from player_news_items pni
    where not exists (select 1 from players p where p.player_id = pni.player_id);

Upvotes: 4

Related Questions