select then delete mysql query

Question

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.

is your referred column was announced as foreign key? if yes, then simply adding on delete cascade option will do the job — Hatik
– Hatik, Commented Oct 26, 2017 at 10:36
See: Why should I provide an MCVE for what seems to me to be a very simple SQL query? — Strawberry
– Strawberry, Commented Oct 26, 2017 at 10:41

Hatik · Accepted Answer · 2017-10-26 10:48:55Z

1

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

answered Oct 26, 2017 at 10:48

Hatik

1,1591 gold badge16 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Josh Murray Over a year ago

Thank you for your answer - works as expected and exactly what I was looking for!

Shyamala Gopalakrishnan · Accepted Answer · 2017-10-26 10:38:08Z

1

will this not do ?

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

answered Oct 26, 2017 at 10:38

Shyamala Gopalakrishnan

5439 silver badges26 bronze badges

1 Comment

Hatik Over a year ago

what about those that were referred to 1, but also were referred by others

Collectives™ on Stack Overflow

select then delete mysql query

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related