CODEWITHSUNDEEP
mysqljoin
Josh Murray
Josh Murray

Reputation: 637

select then delete mysql query

I have a users table and it is possible for one or more users to become related. I have a column in the database called referred and if it has an ID in it, if that ID gets deleted from the database, then the associated users must go too.

Here is my query:

START TRANSACTION;
DELETE FROM users
WHERE id IN 
  ( SELECT a.id
    FROM users a 
      JOIN users b ON a.referred = b.id );

If I remove user Josh Murray with id='1', anyone that matches criteria referred='1' must be deleted also.

Upvotes: 0

Views: 374

Answers (2)

Hatik
Hatik

Reputation: 1159

I would suggest to create table with foreign keys with specifying action ON DELETE for example:

ALTER TABLE users
    ADD CONSTRAINT FK_REFERRED
    FOREIGN KEY (referred)
    REFERENCES users(id)
    ON DELETE CASCADE

So that if for example user with ID = 4 was referencing to user with ID = 1 and another user with ID = 7 is referencing to the first user(ID = 4), when user with ID = 1 is deleted, user with ID = 4 will be also deleted, and user with ID = 7 will be deleted too.

So you wouldn't bother with deleting all other 'children' nodes of the data

Upvotes: 1

Shyamala Gopalakrishnan
Shyamala Gopalakrishnan

Reputation: 533

will this not do ?

Delete from users where (id = 1 or referred = 1)

Upvotes: 1

Related Questions