Question 173179 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – 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?

Tags:

2

Answers


  1. 0

    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;
    Login or Signup to reply.
  2. 0

    You can optionally use the negated EXISTS operator.

    DELETE FROM users
WHERE NOT EXISTS(SELECT 1 
                 FROM customer_cards 
                 WHERE users.id = customer_cards.id)
  AND belongs_to = "ezpay"
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search