MySQL Deleting related rows when they are not in another table

Question

I have a table with over a 100 users that never finished the signin process. I'm trying to delete those users together with all data associated with them.

Since the table relations were not added by the previous developer working on this, I have to do all queries manually.

I came up with the following right now

The last of the 3 queries tries to delete all companies which don't have a company_id in the users table, so all companies that don't have a user associated with them.

The locations and tags constraint kick in, So I have to delete those first. That's how I built this query.. But it's a lot of code and it's hard to read. Any way to improve this?

delete from locations where company_id in (select id from companies 
where not exists (select null from users where users.company_id = 
companies.id));

delete from tags where company_id in (select id from companies where 
not exists (select null from users where users.company_id = 
companies.id));

delete from companies where not exists (select null from users where 
users.company_id = companies.id);

What i've tried

Using SET @todelete = (select...) : but it's throwing errors that my Subquery returns more than 1 record.

Am I doing this the right way? Or can it be simplified?

Thank you!

Please show table structure and maybe some sample data. I can't figure out what you are trying to do from the queries alone. — Tim Biegeleisen, Sep 12 '17 at 14:22

score 1 · Answer 1 · answered Sep 12 '17 at 14:52

_{There's not much difference between your approach and mine. Please consider this just an attempt to make the solution readable}

Since you are trying to delete those data from location \ tags \ companies tables having lonely company_id, first you need to find out which companies are lonely. The definition of lonely company is - A company which don't have any adherence to any user is said to be lonely in this context.

The following query provides id of those lonely companies:

SELECT 
    C.company_id
FROM companies C 
WHERE NOT EXISTS(
    SELECT 
        1
        FROM users U 
        WHERE U.company_id = C.company_id
    );

Now you can delete data from location table associated to the lonely companies using the above helper query.

DELETE 
L
FROM locations L 
INNER JOIN (
    SELECT 
        C.company_id
    FROM companies C 
    WHERE NOT EXISTS(
        SELECT 
        1
        FROM users U 
        WHERE U.company_id = C.company_id
    )
) AS helper
ON L.company_id = helper.company_id;

In this approach you can delete data from other tables too.

See Delete with join in MySQL

MySQL Deleting related rows when they are not in another table

What i've tried

1 Answers1