Search code examples
sqlmysqlleft-joinsql-delete

How to delete users who have not added card?


I have the following query:

select u.*
from users u
left join customers_cards cc
  on cc.user_id = u.id
where u.belongs_to = "ezpay"
  and cc.id is null

It returns users who have not added any card yet. I need to delete these users (from users) table. So here is my delete query:

delete from users
where id in ( select u.id
              from users u
              left join customers_cards cc
                on cc.user_id = u.id
              where u.belongs_to = "ezpay"
                and cc.id is null )

But it throws the following error:

#1093 - You can't specify target table 'users' for update in FROM clause

How can I fix it?


Solution

  • There is no need to use the IN operator because your SELECT statement can be transformed into a DELETE statement:

    delete u.*
    from users u
    left join customers_cards cc
      on cc.user_id = u.id
    where u.belongs_to = "ezpay"
      and cc.id is null;